Skip to content

Instantly share code, notes, and snippets.

@mwinckler

mwinckler/README.markdown

Created May 2, 2012 15:14
Show Gist options
  • Save mwinckler/2577364 to your computer and use it in GitHub Desktop.
Save mwinckler/2577364 to your computer and use it in GitHub Desktop.
Download ZIP
SQL Server: Generate a Data Dictionary
Raw
README.markdown

up_generate_data_dictionary

To use this sproc, after creating it in the database you want to document, execute it in SQL Management Studio and view the "Messages" tab. Copy the contents of the "message" tab out into a text file, save it with a .html extension, and open it in a web browser.

Raw
up_generate_data_dictionary.sql
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[up_generate_data_dictionary]
AS
BEGIN
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><head>'
print '<style type=''text/css''>/* v1.0 | 20080212 */html, body, div, span, applet, object, iframe,h1, h2, h3, h4, h5, h6, p, blockquote, pre,a, abbr, acronym, address, big, cite, code,del, dfn, em, font, img, ins, kbd, q, s, samp,small, strike, strong, sub, sup, tt, var,b, u, i, center,dl, dt, dd, ol, ul, li,fieldset, form, label, legend,table, caption, tbody, tfoot, thead, tr, th, td { margin: 0; padding: 0; border: 0; outline: 0; font-size: 100%; vertical-align: baseline; background: transparent;}body { line-height: 1;}ol, ul { list-style: none;}blockquote, q { quotes: none;}blockquote:before, blockquote:after,q:before, q:after { content: ''''; content: none;}/* remember to define focus styles! */:focus { outline: 0;}/* remember to highlight inserts somehow! */ins { text-decoration: none;}del { text-decoration: line-through;}table { border-collapse: collapse; border-spacing: 0;}body { font-family: Helvetica, Arial, sans-serif; padding: 20px; }h1 { font: bold 32px Helvetica, Arial, sans-serif; }h2 { font: bold 24px Helvetica, Arial, sans-serif; }th { background: #ddd; font-weight: bold; }th, td { padding: 5px; } h1 { margin: 10px 10px 30px; } h2 { margin: 10px 10px 5px; } td { border: 1px solid #666; }</style>'
print '</head><body>'
print '<h1>Data Dictionary: ' + db_name() + '</h1>';
print '<p>Autogenerated from database metadata at '
select GETDATE();
print '</p>'
FETCH NEXT FROM Tbls
INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
Print '<h2>' + @TableName + '</h2>'
PRINT '<table>'
--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 '<th>Column Name</th>'
PRINT '<th>Description</th>'
PRINT '<th>InPrimaryKey</th>'
PRINT '<th>IsForeignKey</th>'
PRINT '<th>DataType</th>'
PRINT '<th>Length</th>'
PRINT '<th>Numeric Precision</th>'
PRINT '<th>Numeric Scale</th>'
PRINT '<th>Nullable</th>'
PRINT '<th>Computed</th>'
PRINT '<th>Identity</th>'
PRINT '<th>Default Value</th>'
--Get the Table Data
SELECT '</tr>',
'<tr>',
'<td><strong>' + CAST(clmns.name AS VARCHAR(35)) + '</strong></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 want 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
END
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment