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:

SELECT s.[name] AS SchemaName,
       t.[name] AS TableName,
       c.[name] AS ColumnName,
       c.is_nullable AS IsNullable,
       typ.[name] AS DataTypeName,
       c.max_length AS MaximumLength,
       c.[precision] AS [Precision],
       c.scale AS Scale, 
       ep.ExtendedPropertyName,
       ep.ExtendedPropertyValue
FROM sys.columns AS c
INNER JOIN sys.tables AS t
ON t.object_id = c.object_id 
INNER JOIN sys.schemas AS s
ON s.schema_id = t.schema_id 
INNER JOIN sys.types AS typ
ON typ.system_type_id = c.system_type_id 
AND typ.user_type_id = c.user_type_id 
OUTER APPLY 
(
    SELECT ep.[name] AS ExtendedPropertyName,
           ep.[value] AS ExtendedPropertyValue
    FROM sys.extended_properties AS ep
    WHERE ep.major_id = c.object_id 
    AND ep.minor_id = c.column_id
) AS ep
WHERE t.is_ms_shipped = 0
AND t.[name] <> N'sysdiagrams'
ORDER BY SchemaName, TableName, ColumnName, ExtendedPropertyName;

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:

ON typ.system_type_id = c.system_type_id 
AND typ.user_type_id = c.system_type_id

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.

2020-05-21