Create a SQL Server Data Dictionary in Seconds using Extended Properties

Problem
I need a way to create a data dictionary that will stay up to date. I make many changes to the database and I spend more time trying to keep the documentation updated than doing database administration.

Solution
You can create a data dictionary natively with SQL Server 2005 for a database in a few seconds if you store the metadata as extended properties. The SQL Server 2005 AdventureWorks sample database has numerous extended properties included, so this database serves as a great example. In this tip we will cover two core topics. First is sample set of scripts to add extended properties for a table as well as a column. The second portion of the tip is T-SQL code to generate the data dictionary in HTML format.

The extended properties can also be viewed by right clicking on an object in SSMS and selecting properties as shown below:

Once your database has data in the extended properties, you are ready to run the query to extract the data. In SQL Server Management Studio select Tools | Options and in the Results to Text section uncheck the “Include column headers in the result set” option. This will keep all of the dashes out of the result set that show up under each column name.

Run the following script to generate the data dictionary and save the results to a file with an '.htm' extension.

Sample T-SQL Script to Generate a Data Dictionary

Set nocount on

DECLARE @TableName nvarchar(35)

DECLARE Tbls CURSOR
FOR

Select distinct Table_name
FROM INFORMATION_SCHEMA.COLUMNS
--put any exclusions here
--where table_name not like '%old'
order by Table_name

OPEN Tbls

PRINT '<HTML><body>'

FETCH NEXT FROM Tbls
INTO @TableName

WHILE @@FETCH_STATUS = 0
BEGIN

PRINT ''
PRINT '<table border="1">'
Print '' + @TableName + ''
PRINT ''
--Get the Description of the table
--Characters 1-250
Select substring(cast(Value as varchar(1000)),1,250) FROM
sys.extended_properties A
WHERE A.major_id = OBJECT_ID(@TableName)
and name = 'MS_Description' and minor_id = 0

--Characters 251-500
Select substring(cast(Value as varchar(1000)),251, 250) FROM
sys.extended_properties A
WHERE A.major_id = OBJECT_ID(@TableName)
and name = 'MS_Description' and minor_id = 0

PRINT '<tr>'
--Set up the Column Headers for the Table
PRINT '<td>Column Name</td>'
PRINT '<td>Description</td>'
PRINT '<td>InPrimaryKey</td>'
PRINT '<td>IsForeignKey</td>'
PRINT '<td>DataType</td>'
PRINT '<td>Length</td>'
PRINT '<td>Numeric Precision</td>'
PRINT '<td>Numeric Scale</td>'
PRINT '<td>Nullable</td>'
PRINT '<td>Computed</td>'
PRINT '<td>Identity</td>'
PRINT '<td>Default Value</td>'

--Get the Table Data
SELECT '</tr>',
'<tr>',
'<td>' + CAST(clmns.name AS VARCHAR(35)) + '</td>',
'<td>' + substring(ISNULL(CAST(exprop.value AS VARCHAR(255)),''),1,250),
substring(ISNULL(CAST(exprop.value AS VARCHAR(500)),''),251,250) + '</td>',
'<td>' + CAST(ISNULL(idxcol.index_column_id, 0)AS VARCHAR(20)) + '</td>',
'<td>' + CAST(ISNULL(
(SELECT TOP 1 1
FROM sys.foreign_key_columns AS fkclmn
WHERE fkclmn.parent_column_id = clmns.column_id
AND fkclmn.parent_object_id = clmns.object_id
), 0) AS VARCHAR(20)) + '</td>',
'<td>' + CAST(udt.name AS CHAR(15)) + '</td>' ,
'<td>' + CAST(CAST(CASE WHEN typ.name IN (N'nchar', N'nvarchar') AND clmns.max_length <> -1
THEN clmns.max_length/2
ELSE clmns.max_length END AS INT) AS VARCHAR(20)) + '</td>',
'<td>' + CAST(CAST(clmns.precision AS INT) AS VARCHAR(20)) + '</td>',
'<td>' + CAST(CAST(clmns.scale AS INT) AS VARCHAR(20)) + '</td>',
'<td>' + CAST(clmns.is_nullable AS VARCHAR(20)) + '</td>' ,
'<td>' + CAST(clmns.is_computed AS VARCHAR(20)) + '</td>' ,
'<td>' + CAST(clmns.is_identity AS VARCHAR(20)) + '</td>' ,
'<td>' + isnull(CAST(cnstr.definition AS VARCHAR(20)),'') + '</td>'
FROM sys.tables AS tbl
INNER JOIN sys.all_columns AS clmns
ON clmns.object_id=tbl.object_id
LEFT OUTER JOIN sys.indexes AS idx
ON idx.object_id = clmns.object_id
AND 1 =idx.is_primary_key
LEFT OUTER JOIN sys.index_columns AS idxcol
ON idxcol.index_id = idx.index_id
AND idxcol.column_id = clmns.column_id
AND idxcol.object_id = clmns.object_id
AND 0 = idxcol.is_included_column
LEFT OUTER JOIN sys.types AS udt
ON udt.user_type_id = clmns.user_type_id
LEFT OUTER JOIN sys.types AS typ
ON typ.user_type_id = clmns.system_type_id
AND typ.user_type_id = typ.system_type_id
LEFT JOIN sys.default_constraints AS cnstr
ON cnstr.object_id=clmns.default_object_id
LEFT OUTER JOIN sys.extended_properties exprop
ON exprop.major_id = clmns.object_id
AND exprop.minor_id = clmns.column_id
AND exprop.name = 'MS_Description'
WHERE (tbl.name = @TableName and
exprop.class = 1) --I don't wand to include comments on indexes
ORDER BY clmns.column_id ASC

PRINT '</tr></table>'

FETCH NEXT FROM Tbls
INTO @TableName
END

PRINT '</body></HTML>'

CLOSE Tbls
DEALLOCATE Tbls