Skip to content

Mr SQL's Blog

Musings on Microsoft SQL Server

Tag Archives: Extended Property

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. We all hate creating documentation, don’t we?! 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.

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:-

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:-

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….

--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

Tags: , , , , ,

%d bloggers like this: