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 for a database in a few seconds if you store the metadata as extended properties. The SQL Server 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 following is a sample script to add extended properties to the database.
Add Extended Properties to a Table and Column
We will add a few extended properties as follows.
/*
The following extended properties already exist in the AdventureWorks database. There is no need to run the
script against the database in order for the remaining samples to work.
*/
USE [AdventureWorks]
GO
--Script to add an Extended Property to the Table
EXEC sys.sp_addextendedproperty
@name=N'MS_Description',
@value=N'Street address information for customers, employees, and vendors.' ,
@level0type=N'SCHEMA',
@level0name=N'Person', --Schema Name
@level1type=N'TABLE',
@level1name=N'Address' --Table Name
GO
--Script to add an Extended Property to a column
EXEC sys.sp_addextendedproperty
@name=N'MS_Description',
@value=N'First street address line.' ,
@level0type=N'SCHEMA',
@level0name=N'Person', --Schema Name
@level1type=N'TABLE',
@level1name=N'Address',--Table Name
@level2type=N'COLUMN',
@level2name=N'AddressLine1'--Column Name
GO
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.
Data Dictionary in HTML Format
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
You can add additional filters in the WHERE clause if you want to focus on specific items in the 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 '<br>'
PRINT '<table border="1">'
Print '<B>' + @TableName + '</B>'
PRINT '<br>'
--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><b>Column Name</b></td>'
PRINT '<td><b>Description</b></td>'
PRINT '<td><b>InPrimaryKey</b></td>'
PRINT '<td><b>IsForeignKey</b></td>'
PRINT '<td><b>DataType</b></td>'
PRINT '<td><b>Length</b></td>'
PRINT '<td><b>Numeric Precision</b></td>'
PRINT '<td><b>Numeric Scale</b></td>'
PRINT '<td><b>Nullable</b></td>'
PRINT '<td><b>Computed</b></td>'
PRINT '<td><b>Identity</b></td>'
PRINT '<td><b>Default Value</b></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
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
Next Steps
- The script can be scheduled to run as a job, so you will never have to worry about updating the documentation manually.
- Since you are storing the documentation in the database, you never have to worry about having multiple copies and trying to figure out which one is current. It also gets backed up with the database. So the value is very high.
- The script will run without adding the extended properties, but obviously you will be missing the descriptions, so take some time and start adding the information to your environment.
