//
you're reading...
BI, Data Warehouse, SQL Server, T-SQL

Automatically Generating a Data Dictionary Using Extended Properties

“Documentation” The bane of our existence and we all hate creating it, don’t we?! I am always searching for ways to improve documentation through automation. Awhile ago I stumbled upon utilizing the extended properties of sql objects to store change management info. The T-SQL script at the bottom of the page produces a simple data dictionary for a SQL Server Database, along with descriptions of its objects. It produces HTML output that can be uploaded to a web server, or you could copy and paste into Word. The example includes tables, columns and stored procedures, but it would be fairly simple to tweak the query to include additional objects such as views, users, primary and foreign keys etc. This technique can be modified and enhanced to store almost any type of metadata you want.

Before running the query, it is highly recommended to add descriptions to the tables, columns and procedures. You can do this within SQL Server Management Studio (right click the object, select Properties and then the Extended Properties tab), but I find it easier to use the built-in sys.sp_addextendedproperty procedure.

Adding a Description to a Table:-

1
2
3
4
5
6
7
8
EXEC sys.sp_addextendedproperty
@name=N'Description',
@value=N'Add Your Description Here' ,
@level0type=N'SCHEMA',
@level0name=N'dbo',
@level1type=N'TABLE',
@level1name=N'Your Table Name Here'
GO

Adding a Description to a Column:-

1
2
3
4
5
6
7
8
9
10
EXEC sys.sp_addextendedproperty
@name=N'Description',
@value=N'Add Your Description Here' ,
@level0type=N'SCHEMA',
@level0name=N'dbo',
@level1type=N'TABLE',
@level1name=N'Your Table Name Here',
@level2type=N'COLUMN',
@level2name=N'Your Column Name Here'
GO

Now we can generate the documentation….

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
--Ensure no additional lines are printed to the output
SET NOCOUNT ON
--Declare Variables
DECLARE @dbase varchar(50)
DECLARE @full_table_name varchar(100)
DECLARE @table_name varchar(100)
DECLARE @table_desc varchar(500)
DECLARE @table_date varchar(500)
-- Declare Variables for HTML Output
DECLARE @column_html varchar(max)
DECLARE @index_html varchar(max)
DECLARE @proc_html varchar(max)
--Set database name
SET @dbase = (SELECT DB_NAME() As 'Database Name')
-- Start HTML Generation
PRINT '<HTML><body>'
PRINT '<h1>' + @dbase + '</h1>'
-- Setup Table cursor
DECLARE cr_tables CURSOR READ_ONLY FOR
select distinct t.name
from sys.TABLES t
left join sys.extended_properties x
on t.object_id = x.major_id
where t.name <> 'sysdiagrams'
order by t.name asc
-- Open Cursor
OPEN cr_tables
FETCH NEXT FROM cr_tables
INTO @table_name
WHILE @@FETCH_STATUS = 0
BEGIN
-- Select extended table properties
select @full_table_name = (s.name + '.' + t.name), @table_desc = convert(varchar(1000), x.value), @table_date = modify_date
from sys.TABLES t
left join sys.extended_properties x
on t.object_id = x.major_id
left join sys.schemas s
on t.schema_id = s.schema_id
where t.name = @table_name
order by t.name asc
-- Print Table Properties
PRINT '<h2>' + @full_table_name + '</h2>'
PRINT '<b>Description:-</b> ' + @table_desc + '<br>'
PRINT '<b>Last Modified:-</b> ' + @table_date
-- Print HTML Table Columns
select @column_html = '<h3>Columns</h3><table border = "1" cellpadding = "5" cellspacing = "0">
<tr><td><b>Column</b></td><td><b>Description</b></td><td><b>Column Ordinal Position</b></td>
<td><b>Data Type</b></td>
<td><b>Max Length</b></td>
<td><b>Precision</b></td>
<td><b>Nulls Allowed</b></td>
<td><b>Identity</b></td></tr>'
-- Get SQL Columns
select @column_html = @column_html + '<tr><td>' + convert(varchar(100), c.name) +'</td><td>' + ISNULL(convert(varchar(200), x.value), 'No description available') + '</td><td>' + convert(varchar(3), column_id) + '</td><td>' + ty.name + '</td><td>' + convert(varchar(4), c.max_length) + '</td><td>' + convert(varchar(4), c.precision) + '</td><td>' +
convert(varchar(3), CASE c.is_nullable
WHEN 1 THEN 'Yes'
ELSE 'No'
END) + '</td><td>' +
convert(varchar(3),CASE c.is_identity
WHEN 1 THEN 'Yes'
ELSE 'No'
END) + '</td></tr>'
from sys.columns c
left join sys.extended_properties x
on c.object_id = x.major_id
and c.column_id = x.minor_id
left join sys.tables t
on c.object_id = t.object_id
left join sys.types ty
on c.system_type_id = ty.user_type_id
where type_desc = 'USER_TABLE'
and t.name = @table_name
order by t.name, c.column_id asc
PRINT @column_html + '</table>'
-- Get Index Info
select @index_html = '<h3>Indexes</h3><table border = "1" cellpadding = "5" cellspacing = "0">
<tr><td><b>Index</b></td><td><b>Index Type</b></td><td><b>Is Primary Key</b></td>
<td><b>Fill Factor</b></td></tr>'
select @index_html = @index_html + '<tr><td>' + i.name + '</td><td>' + i.type_desc
+ '</td><td>' +
convert(varchar(3), (CASE i.is_primary_key
WHEN 1 THEN 'Yes'
ELSE 'No'
END))
+ '</td><td>' +
convert(varchar(3), fill_factor) + '</td></tr>'
from sys.tables t
right join sys.indexes i
on t.object_id = i.object_id
where i.name is not null
and t.name is not null
and t.name = @table_name
order by t.name asc, Is_Primary_Key desc, i.name asc
PRINT @index_html + '</table><br>'
FETCH NEXT FROM cr_tables
INTO @table_name
END
-- Generate HTML for Stored Procedures
select @proc_html = '<br><h2>Stored Procedures</h2><table border = "1" cellpadding = "5" cellspacing = "0">
<tr> <td><b>Stored Procedure</b></td><td><b>Description</b></td><td><b>Last Modified Date</b></td></tr>'
select @proc_html = @proc_html + '<tr><td>' + p.name + '</td><td>' + convert(varchar(100), x.value) + '</td><td>' + convert(varchar(50), p.modify_date) + '</td></tr>'
from sys.procedures p
left join sys.extended_properties x
on p.object_id = x.major_id
where p.is_ms_shipped = 0
and p.name not like 'sp_%'
PRINT @proc_html
PRINT '</table>'
PRINT '</HTML></body>'
close cr_tables
deallocate cr_tables
SET NOCOUNT OFF

 

Bon Appetit!

L.Dixon Galler

Advertisements

About ldgaller

Accomplished Data Warehouse Architect, DBA and Software Architect with over 15 years of professional experience and demonstrated success designing and implementing solutions that improve business functionality and productivity. Highly diverse technical background with proven ability to design, develop and implement technology on an enterprise level. I approach all projects with passion, diligence, integrity, and exceptional aptitude.

Discussion

No comments yet.

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

%d bloggers like this: