SQL: List all SQL Server columns and their extended properties

I answered a forum question the other day about how to list all the columns in a SQL Server database. That's straightforward enough, but they also asked for all the extended properties for the column.

In case you need to do this, here's some code:

How it works

I start with the sys.columns view and join it to sys.tables and sys.schemas, to get the schema and table name. The other reason is to make sure it's not a Microsoft-supplied table. I also wanted to exclude the sysdiagrams table that is created when you first create a database diagram using SQL Server Management Studio. (Curiously, that one's not flagged as a Microsoft-supplied table).

The next join was to sys.types to get details of the data type. Notice that the join is on both system_type_id and user_type_id. You need to do that to allow for aliased data types. If you want the underlying base-type, change the ON clause to this:

While the first query would return names like sysname, the alternative ON clause would return same column as the underlying type like nvarchar(128).

Finally, I'm outer applying a query that finds the extended properties for a column. The sys.extended_properties view provides that. It can work with many different types of objects, so you need to closely define what you're looking for. The major_id is the object (i.e. the table) and the minor_id is the column_id. I've used an OUTER APPLY to make sure the column comes back, even if it doesn't have any extended properties.

If you only want columns with extended properties, you could change the OUTER APPLY to a CROSS APPLY instead.




Leave a Reply

Your email address will not be published. Required fields are marked *