Little SQL Server Tricks: Get All Columns in Your Database

I had to check some inconsistencies in our database naming convention and needed a list of all column names inside a database. While you can find multiple solutions with Google, it took me a while until I found one that worked and was flexible enough for all my needs.

If you need to get a list of your columns, I suggest you use the System Information Schema Views that offers all sorts of metadata on your database. For my use-case the COLUMNS view had all I needed:

1
2
3
4
5
6
7
8
SELECT 
    TABLE_SCHEMA,
    TABLE_NAME,
    COLUMN_NAME,
    DATA_TYPE,
    COLUMN_DEFAULT,
    CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS

Get a list of all column names

This query gives you the names of your columns, their data type and the default values. Should you need something more, there are other views that may offer you that data.