SQL: List all columns in a SQL Server table with their extended properties

I wish SQL Server had more metadata associated with columns in tables, and with objects in general. The closest thing we have to that is the use of extended properties.

Other Databases

In other databases that I've worked with, there are richer properties held for each column. For example in Progress, as well as the normal data type and nullability, there were options like:

Formatting mask (how this column is normally formatted)
Prompt (what question to automatically ask the user – gives you a chance to explain what you're asking)

and much more. This type of thing drastically cut down the amount of time it took to build applications using the database, and provided a great level of consistency.

When I first started working with SQL Server, I was struck by how little of this type of info was present.

Extended Properties

SQL Server offers extended properties. You can do this sort of thing with them, but I find the following issues:

Scripting tools often omit them. They not part of the core info about a column and it's easy to end up without them.
There aren't standards for what you put in them e.g. there's no agreed standard that says what to call a description property, or more interesting perhaps, a property that explains why an index exists.

Some databases I work with do have them though. And I wish they were easier to work with. I often want to list them. The following dynamic code will find all the columns in the DB and return basic info about them. And the pivot that follows it will provide a column in the output for each extended property name that's present. For each column, it will show the values for any of these properties.

DECLARE @ExtendedPropertyColumnList nvarchar(max)
= 
(
    SELECT STRING_AGG(QUOTENAME(ep.EPName), ',')
    FROM 
    (
        SELECT DISTINCT ep.[name] AS EPName 
        FROM sys.extended_properties AS ep
        INNER JOIN sys.columns AS c
        ON c.object_id = ep.major_id
        AND c.column_id = ep.minor_id 
        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 
        WHERE t.is_ms_shipped = 0
        AND t.[name] <> N'sysdiagrams'
    ) AS ep);

DECLARE @SQL nvarchar(max) = N'
WITH ColumnsToList
AS
(
    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.[name] AS ExtendedPropertyName,
           CAST(ep.[value] AS nvarchar(max)) AS 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 
    LEFT OUTER JOIN sys.extended_properties AS ep
    ON ep.major_id = c.object_id 
    AND ep.minor_id = c.column_id 
    WHERE t.is_ms_shipped = 0
    AND t.[name] <> N''sysdiagrams''
)
SELECT *
FROM 
(
    SELECT * 
    FROM ColumnsToList 
) AS ctl
PIVOT 
(
    MAX(ExtendedPropertyValue)
    FOR ExtendedPropertyName IN (' + @ExtendedPropertyColumnList + N')
) AS epp;';

EXEC (@SQL);
GO

DECLARE @ExtendedPropertyColumnList nvarchar(max)

(

SELECT STRING_AGG(QUOTENAME(ep.EPName), ',')

FROM

(

SELECT DISTINCT ep.[name] AS EPName

FROM sys.extended_properties AS ep

INNER JOIN sys.columns AS c

ON c.object_id = ep.major_id

AND c.column_id = ep.minor_id

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

WHERE t.is_ms_shipped = 0

AND t.[name] <> N'sysdiagrams'

) AS ep);

DECLARE @SQL nvarchar(max) = N'

WITH ColumnsToList

(

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.[name] AS ExtendedPropertyName,

CAST(ep.[value] AS nvarchar(max)) AS 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

LEFT OUTER JOIN sys.extended_properties AS ep

ON ep.major_id = c.object_id

AND ep.minor_id = c.column_id

WHERE t.is_ms_shipped = 0

AND t.[name] <> N''sysdiagrams''

)

SELECT *

FROM

(

SELECT *

FROM ColumnsToList

) AS ctl

PIVOT

(

MAX(ExtendedPropertyValue)

FOR ExtendedPropertyName IN (' + @ExtendedPropertyColumnList + N')

) AS epp;';

EXEC (@SQL);

The main image above shows the output from the WideWorldImporters database.

If you don't want the output with the properties pivoted, you could also use the code that I put in the following post.

Other Databases

Extended Properties

Leave a Reply Cancel reply