Data Dictionary built from the DB itself

Much like most developers, I hate doing documentation. It’s a boring chore, which must be done from time to time; but it’s tedious busy-work, and involves no imagination or creativity… So to ease the pain of creating SOME of the documentation associated with the role of a DBA, I created (through the use of some code I found online, and modified) the following script.
This code uses Extended Properties of Fields (can be found while in design of a Table, the Description Property of each field) and Tables (can be modified using the following script).
Using these Extended Properties along with other information that can be found in the systables, I create a table variable storing all information about each field and table in the Database.
The code (to add Extended Properties to the table):
DECLARE @t VARCHAR(128)
DECLARE @v sql_variant
SET @t = ‘TableName’
SET @v = N’Long Description fo what the table does’
EXEC sp_dropextendedproperty ‘MS_Description’, ‘schema’, dbo, N’table’, @t, null, null;
EXEC sp_addextendedproperty N’MS_Description’, @v, N’user’, N’dbo’,N’table’, @t, NULL, NULL
The code (to run the DataDictionary):
DECLARE @Columns TABLE
(
TABLE_TYPE VARCHAR(128),
TABLE_CATALOG VARCHAR(128),
TABLE_SCHEMA VARCHAR(128),
TABLE_NAME VARCHAR(128),
TABLE_DESCRIPTION VARCHAR(1000),
RecordCount INT,
COLUMN_NAME VARCHAR(128),
ORDINAL_POSITION INT,
DATA_TYPE VARCHAR(128),
COLUMN_DESCRIPTION VARCHAR(1000),
COLUMN_DEFAULT VARCHAR(255),
IS_NULLABLE VARCHAR(3),
FieldPrecision INT,
CHARACTER_SET_NAME VARCHAR(128),
COLLATION_NAME VARCHAR(128),
PK BIT,
FKey_NAME VARCHAR(128),
PKey_NAME VARCHAR(128),
PKEY_TABLE VARCHAR(128),
PKEY_COLUMN VARCHAR(128),
IsIdentity VARCHAR(3),
IsRowGuidCol VARCHAR(3),
IsFullTextIndexed VARCHAR(3),
CHECK_CONSTRAINT VARCHAR(255),
INDEX_CONSTRAINT VARCHAR(255)
)
–Set Primary table/field values
INSERT INTO @Columns
(TABLE_TYPE,TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME,RecordCount,COLUMN_NAME,ORDINAL_POSITION,DATA_TYPE,
COLUMN_DEFAULT,IS_NULLABLE,FieldPrecision,CHARACTER_SET_NAME,COLLATION_NAME, FKey_NAME,
IsIdentity, IsRowGuidCol, IsFullTextIndexed)
SELECT T.TABLE_TYPE, T.TABLE_CATALOG, T.TABLE_SCHEMA, T.TABLE_NAME, max(i.rows),
C.COLUMN_NAME, C.ORDINAL_POSITION,
DATA_TYPE + ISNULL(‘(‘ + CAST([CHARACTER_MAXIMUM_LENGTH] AS VARCHAR(255)) + ‘)’, ”),
COLUMN_DEFAULT, IS_NULLABLE, IsNull(NUMERIC_PRECISION,DATETIME_PRECISION) AS FieldPrecision,
CHARACTER_SET_NAME, COLLATION_NAME, U.CONSTRAINT_NAME,
COLUMNPROPERTY(OBJECT_ID(C.TABLE_NAME), C.COLUMN_NAME, ‘IsIdentity’),
COLUMNPROPERTY(OBJECT_ID(C.TABLE_NAME), C.COLUMN_NAME, ‘IsRowGuidCol’),
COLUMNPROPERTY(OBJECT_ID(C.TABLE_NAME), C.COLUMN_NAME, ‘IsFulltextIndexed’)
FROM sysindexes i, INFORMATION_SCHEMA.Tables T JOIN INFORMATION_SCHEMA.Columns C
ON T.TABLE_SCHEMA = C.TABLE_SCHEMA
AND T.TABLE_NAME = C.TABLE_NAME
LEFT OUTER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS U
ON T.TABLE_SCHEMA = U.TABLE_SCHEMA
AND T.TABLE_NAME = U.TABLE_NAME
AND C.COLUMN_NAME = U.COLUMN_NAME
AND U.CONSTRAINT_NAME LIKE ‘FK_%’
WHERE (T.TABLE_NAME <> ‘dtproperties’) AND (T.TABLE_NAME NOT LIKE ‘sys%’)
AND (TABLE_TYPE = ‘BASE TABLE’) AND (T.TABLE_NAME = object_name(i.id))
GROUP BY T.TABLE_TYPE, T.TABLE_CATALOG, T.TABLE_SCHEMA, T.TABLE_NAME,
C.COLUMN_NAME, C.ORDINAL_POSITION,
DATA_TYPE + ISNULL(‘(‘ + CAST([CHARACTER_MAXIMUM_LENGTH] AS VARCHAR(255)) + ‘)’, ”),
COLUMN_DEFAULT, IS_NULLABLE, IsNull(NUMERIC_PRECISION,DATETIME_PRECISION),
CHARACTER_SET_NAME, COLLATION_NAME, U.CONSTRAINT_NAME,
COLUMNPROPERTY(OBJECT_ID(C.TABLE_NAME), C.COLUMN_NAME, ‘IsIdentity’),
COLUMNPROPERTY(OBJECT_ID(C.TABLE_NAME), C.COLUMN_NAME, ‘IsRowGuidCol’),
COLUMNPROPERTY(OBJECT_ID(C.TABLE_NAME), C.COLUMN_NAME, ‘IsFulltextIndexed’)
ORDER BY T.TABLE_TYPE, T.TABLE_SCHEMA, T.TABLE_NAME, C.ORDINAL_POSITION
–Set CHECK_CONSTRAINT values
UPDATE @Columns
SET CHECK_CONSTRAINT = CC.CHECK_CLAUSE
FROM @Columns AS C
INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS U
ON C.TABLE_NAME = U.TABLE_NAME
AND C.COLUMN_NAME = U.COLUMN_NAME
LEFT OUTER JOIN INFORMATION_SCHEMA.CHECK_CONSTRAINTS AS CC
ON U.CONSTRAINT_NAME = CC.CONSTRAINT_NAME
–Set INDEX_CONSTRAINT values
UPDATE @Columns
SET INDEX_CONSTRAINT = U.CONSTRAINT_NAME
FROM @Columns AS C
INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS U
ON U.TABLE_NAME = C.TABLE_NAME
AND U.COLUMN_NAME = C.COLUMN_NAME
AND U.CONSTRAINT_NAME LIKE ‘IX_%’
–Set PK values
UPDATE @Columns
SET PK = -1
FROM @Columns AS C
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS K
ON C.TABLE_NAME = K.TABLE_NAME
AND C.COLUMN_NAME = K.COLUMN_NAME
AND K.CONSTRAINT_NAME LIKE ‘PK_%’
–Set PKey_Name values
UPDATE @Columns
SET PKey_Name = RC.UNIQUE_CONSTRAINT_NAME
FROM @Columns AS C
INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RC
ON RC.CONSTRAINT_NAME = C.FKey_NAME
–Set PKEY_TABLE values
UPDATE @Columns
SET PKEY_TABLE = U.[TABLE_NAME],
PKEY_COLUMN = U.[COLUMN_NAME]
FROM @Columns AS C
INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS U
ON U.CONSTRAINT_NAME = C.PKEY_NAME
–Set TABLE_DESCRIPTION and COLUMN_DESCRIPTION values
DECLARE @Table VARCHAR(128)
DECLARE @Field VARCHAR(128)
DECLARE Tables CURSOR FOR
SELECT DISTINCT TABLE_NAME
FROM @Columns
WHERE TABLE_TYPE = ‘BASE TABLE’
OPEN Tables
FETCH NEXT FROM Tables INTO @Table
WHILE @@FETCH_STATUS = 0
BEGIN
–Set TABLE_DESCRIPTION values
UPDATE @Columns
SET TABLE_DESCRIPTION = CAST(TD.[Value] AS VARCHAR(1000))
FROM ::fn_listextendedproperty(N’MS_Description’, N’user’, ‘dbo’, ‘TABLE’, default, default, default) AS TD
WHERE TD.ObjName = @Table
AND TABLE_NAME = @Table
IF (SELECT COUNT(*) FROM ::fn_listextendedproperty(‘MS_Description’, ‘user’, ‘dbo’, N’TABLE’, @Table, ‘COLUMN’, default)) > 0
BEGIN
DECLARE Fields CURSOR FOR
SELECT DISTINCT COLUMN_NAME
FROM @Columns
WHERE TABLE_TYPE = ‘BASE TABLE’
AND TABLE_NAME = @Table
OPEN Fields
FETCH NEXT FROM Fields INTO @Field
WHILE @@FETCH_STATUS = 0
BEGIN
–Set COLUMN_DESCRIPTION values
UPDATE @Columns
SET COLUMN_DESCRIPTION = CAST(CD.[Value] AS VARCHAR(1000))
FROM ::fn_listextendedproperty(‘MS_Description’, ‘user’, ‘dbo’, N’TABLE’, @Table, ‘COLUMN’, default) as CD
WHERE CD.ObjName = @Field
AND TABLE_NAME = @Table
AND COLUMN_NAME = @Field
FETCH NEXT FROM Fields INTO @Field
END
CLOSE Fields
DEALLOCATE Fields
END
FETCH NEXT FROM Tables INTO @Table
END
CLOSE Tables
DEALLOCATE Tables
SELECT *
FROM @Columns
Advertisements

About Rodibidably

Jeff Randall is a frequent volunteer for free-thought organizations, including the Center For Inquiry – DC. Having been blogging since January 2008, he decided that a community of bloggers would be an interesting new experience (or at the very least a fun way to annoy his friends into reading his posts more frequently). Since finding out about about the existence of, and then joining, the atheist/skeptic community in 2007 he has been committed to community activism, critical thinking in all aspects of life, science, reason, and a fostering a secular society.
This entry was posted in SQL Server. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s