Querying Extended Properties on SQL Server Columns

Using extended properties is a terrific way for a database to be self-documenting.  I find extended properties on columns to be particularly useful.  The extended property may contain whatever you need, but usually it serves to describe the column and may also provide sample values or situations when a particular value would be used.  For example:

Contains a value of 1 if the day is the last day in the academic year. Otherwise, 0.    

I found myself wanting to query some existing extended properties so I could better understand some fields.  Column level extended properties are the focus of the following query:


     SysTbls.name AS [Table Name]

    ,SysCols.name AS [Column Name]

    ,ExtProp.value AS [Extended Property]

    ,Systyp.name AS [Data Type]

    ,CASE WHEN Systyp.name IN('nvarchar','nchar')

               THEN (SysCols.max_length / 2)

          WHEN Systyp.name IN('char')

               THEN SysCols.max_length

          ELSE NULL

          END AS 'Length of Column'

    ,CASE WHEN SysCols.is_nullable = 0

               THEN 'No'

          WHEN SysCols.is_nullable = 1

               THEN 'Yes'

          ELSE NULL

          END AS 'Column is Nullable'   

    ,SysObj.create_date AS [Table Create Date]

    ,SysObj.modify_date AS [Table Modify Date]

FROM sys.tables AS SysTbls

   LEFT JOIN sys.extended_properties AS ExtProp

         ON ExtProp.major_id = SysTbls.[object_id]

   LEFT JOIN sys.columns AS SysCols

         ON ExtProp.major_id = SysCols.[object_id]

         AND ExtProp.minor_id = SysCols.column_id

   LEFT JOIN sys.objects as SysObj

         ON SysTbls.[object_id] = SysObj.[object_id]

   INNER JOIN sys.types AS SysTyp

         ON SysCols.user_type_id = SysTyp.user_type_id

WHERE class = 1 --Object or column

  AND SysTbls.name IS NOT NULL

  AND SysCols.name IS NOT NULL

The results of the above query:

I went on to create a simple SSRS report from it, adding the Table Name as a multi-select parameter:

Hopefully you have access to a tool like PowerDesigner to generate this type of report.  If you can’t spring for a data modeling tool this technique could be useful.  Or, if you have a situation where the data model could get out of date with the database structure (i.e., it’s not mandatory that the DDL be generated from the data model), this type of query could be useful.

Finding More Information

Viewing Extended Properties  

Using Extended Properties on Database Objects

Easy Extended Properties