Auto Generating a Data Dictionary – Part 1

Wouldn’t it be great to be able to generate a data dictionary for a SQL 2008 Database? You can, with this handy slab.

Running this code up against a DB will give you lots of information, unless you don’t set it in the first place. At the very least, you should be creating keys and relationships. If you’re into “Agile”, this idea will probably cause you to foam at the mouth, but I’m okay with that

All of this assumes – with its inherent dangers – that you have SSMS open, and you are in the database for which you want to create the dictionary. Here’s some preliminary pointers:

• To set a table’s description: right click the table, select Properties. Switch to the Extended Properties page and create a Property named “MS_Description”. The value to what you want to describe the table.
• To set a column’s description: Open the table in the Design view and select the column. Enter the description in the Column Properties pane under Description.
• To set a Primary Key’s description: With the table’s design view open, open the Index\Keys dialog. Set the comments in the Description field under Identity.
• To set a Foreign Key’s description: With the table’s design view open, open the Relationships dialog. Set the comments in the Description field under Identity.

Note: Tested on SQL Server 2008. If you find something that doesn’t work on an older version, shoot me updated code. Otherwise, I’ll eventually add the code when I get around to trying to document one of the older systems.

Run this first to set up the infrastructure:

CREATE TABLE [dbo].[DataDictionaryExclusions](
	[ExclusionID] [int] IDENTITY(1,1) NOT NULL,
	[ObjectName] [varchar](100) NOT NULL,
	[ObjectType] [varchar](25) NOT NULL,
 CONSTRAINT [PK_DataDictionaryExclusions] PRIMARY KEY CLUSTERED
(
	[ExclusionID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

INSERT INTO DataDictionaryExclusions (ObjectName, ObjectType)
VALUES	('DataDictionaryExclusions', 'Table'),
		('GenerateDataDictionary', 'Procedure')

Then Add this stored procedure:

CREATE PROCEDURE [dbo].[GenerateDataDictionary]
-- =============================================
-- Author:		Corey Furman
-- Create date: 20 Nov 2008
-- Description:	Generates a data dictionary
-- =============================================
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	/* variable set up */
	DECLARE @LineFeed	AS VARCHAR(2)
	DECLARE @Tab		AS CHAR
	DECLARE @SpaceChar	AS CHAR
	DECLARE @WhiteSpace AS VARCHAR(4)
	SET @LineFeed		= CHAR(10) + CHAR(13)
	SET @Tab			= CHAR(9)
	SET @SpaceChar		= CHAR(32)
	SET @WhiteSpace		= @LineFeed + @Tab + @SpaceChar
	DECLARE @Period		AS CHAR
	SET @Period			= '.'

	/* table listing */
	SELECT	[TableName]		= T.Name,
			[Description]	= COALESCE(E.value, ''),
			[Type]			= 'Table'
	FROM sys.tables T
	LEFT JOIN sys.extended_properties E ON E.major_id = T.object_id
		AND E.minor_id = 0
	WHERE T.name NOT IN (SELECT ObjectName FROM dbo.DataDictionaryExclusions WHERE ObjectType = 'Table')

	UNION

	SELECT	[TableName]		= V.Name,
			[Description]	= COALESCE(E.value, ''),
			[Type]			= 'View'
	FROM sys.views V
	LEFT JOIN sys.extended_properties E ON E.major_id = V.object_id
		AND E.minor_id = 0
	WHERE V.name NOT IN (SELECT ObjectName FROM dbo.DataDictionaryExclusions WHERE ObjectType = 'View')
	ORDER BY T.Name

	/* column listing */
	SELECT	DISTINCT
			[Column]		= C.COLUMN_NAME,
			[Table]			= C.TABLE_NAME,
			[Position]		= C.ORDINAL_POSITION,
			DataType		= CASE	WHEN C.DATA_TYPE = 'varchar' THEN C.DATA_TYPE + '(' + CAST(C.CHARACTER_MAXIMUM_LENGTH AS VARCHAR(10)) + ')'
									WHEN C.DATA_TYPE = 'nvarchar' THEN C.DATA_TYPE + '(' + CAST(C.CHARACTER_MAXIMUM_LENGTH AS VARCHAR(10)) + ')'
									WHEN C.DATA_TYPE = 'text' THEN C.DATA_TYPE + '(' + CAST(C.CHARACTER_MAXIMUM_LENGTH AS VARCHAR(10)) + ')'
									WHEN C.DATA_TYPE = 'ntext' THEN C.DATA_TYPE + '(' + CAST(C.CHARACTER_MAXIMUM_LENGTH AS VARCHAR(10)) + ')'
									WHEN C.DATA_TYPE = 'char' THEN C.DATA_TYPE + '(' + CAST(C.CHARACTER_MAXIMUM_LENGTH AS VARCHAR(10)) + ')'
									WHEN C.DATA_TYPE = 'numeric' THEN C.DATA_TYPE + '(' + CAST(C.NUMERIC_PRECISION AS VARCHAR(10)) + ', ' + CAST(C.NUMERIC_SCALE AS VARCHAR(10)) + ')'
									WHEN C.DATA_TYPE = 'decimal' THEN C.DATA_TYPE + '(' + CAST(C.NUMERIC_PRECISION AS VARCHAR(10)) + ', ' + CAST(C.NUMERIC_SCALE AS VARCHAR(10)) + ')'
									WHEN C.DATA_TYPE = 'float' THEN C.DATA_TYPE + '(' + CAST(C.NUMERIC_PRECISION AS VARCHAR(10)) + ', ' + CAST(C.NUMERIC_SCALE AS VARCHAR(10)) + ')'
							  ELSE C.DATA_TYPE
							  END,
			DefaultValue	= ISNULL(C.COLUMN_DEFAULT, ''),
			Nullable		= C.IS_NULLABLE,
			[Description]	= ISNULL(E.value, '')
	FROM information_schema.columns C
	LEFT JOIN sys.syscolumns C2 ON C2.name = C.COLUMN_NAME
	LEFT JOIN sys.extended_properties E ON E.major_id = C2.id
		AND E.minor_id = C2.colid
	WHERE C.TABLE_NAME NOT IN (SELECT ObjectName FROM dbo.DataDictionaryExclusions WHERE ObjectType IN ('Table', 'View'))
	ORDER BY C.COLUMN_NAME, C.TABLE_NAME

	/* relationships */
	SELECT	[Constraint]		= T.CONSTRAINT_NAME,
			[Type]				= LOWER(REPLACE(T.CONSTRAINT_TYPE, ' KEY', '')),
			[Table]				= T.TABLE_NAME,
			[Column]			= C1.COLUMN_NAME,
			ConstrainedBy		= CASE WHEN LOWER(REPLACE(T.CONSTRAINT_TYPE, ' KEY', '')) = 'foreign' THEN C2.TABLE_NAME + '.' + C2.COLUMN_NAME
								  ELSE ''
								  END,
			[Description]		= CASE WHEN LOWER(REPLACE(T.CONSTRAINT_TYPE, ' KEY', '')) = 'primary' THEN ISNULL(E1.value, '')
								  ELSE ISNULL(E2.value, '')
								  END,
			[DeleteAction]		= LOWER(REPLACE(ISNULL(F.delete_referential_action_desc, ''), '_', ' ')),
			[UpdateAction]		= LOWER(REPLACE(ISNULL(F.update_referential_action_desc, ''), '_', ' '))
	FROM information_schema.TABLE_CONSTRAINTS T
	JOIN information_schema.CONSTRAINT_COLUMN_USAGE C1 ON C1.CONSTRAINT_NAME = T.CONSTRAINT_NAME
	LEFT JOIN information_schema.REFERENTIAL_CONSTRAINTS R ON R.CONSTRAINT_NAME = T.CONSTRAINT_NAME
	LEFT JOIN information_schema.CONSTRAINT_COLUMN_USAGE C2 ON C2.CONSTRAINT_NAME = R.UNIQUE_CONSTRAINT_NAME
	LEFT JOIN sys.key_constraints K ON K.name = T.CONSTRAINT_NAME
	LEFT JOIN sys.extended_properties E1 ON E1.major_id = K.object_id
	LEFT JOIN sys.foreign_keys F ON F.name = T.CONSTRAINT_NAME
	LEFT JOIN sys.extended_properties E2 ON E2.major_id = F.object_id
	WHERE T.TABLE_NAME NOT IN (SELECT ObjectName FROM dbo.DataDictionaryExclusions WHERE ObjectType = 'Table')
	ORDER BY T.TABLE_NAME, T.CONSTRAINT_TYPE DESC

	/* stored procedures */
	SELECT name
	FROM sys.procedures
	ORDER BY name

	/* procedures to object cross reference */
	SELECT * FROM (
		SELECT DISTINCT
			[SProc Name]	= P.name,
			[Object Name]	= O.name,
			[Obj Type]		= CASE	WHEN O.xtype = 'u' THEN 'Table'
									WHEN O.xtype = 'v' THEN 'View'
									WHEN O.xtype = 'p' THEN 'Procedure'
							  END
		FROM sysobjects O
		INNER JOIN (
			SELECT A.name, B.text
			FROM sysobjects A INNER JOIN syscomments B ON A.id = B.id
		) P ON P.text LIKE '%[' + @WhiteSpace + @Period + ']' + O.name + '[' + @WhiteSpace + ']%'
			AND O.xtype IN ('u', 'v', 'p')
			AND P.name <> O.name
	) data
	WHERE [SProc Name] NOT IN (SELECT ObjectName FROM dbo.DataDictionaryExclusions WHERE ObjectType = 'Procedure')
	ORDER BY [SProc Name], [Obj Type], [Object Name]

END

You run it like this:

EXEC dbo.GenerateDataDictionary

Part 2 will be an app that will dump this data to an Excel workbook… believe me, you won’t want to miss it.