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:
SELECT
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.