Report table and column-level extended properties

IF EXISTS (select * from INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME='DataDictionaryTableReport' AND ROUTINE_TYPE='PROCEDURE')
DROP PROCEDURE DataDictionaryTableReport
GO


CREATE PROCEDURE DataDictionaryTableReport
@TABLE_NAME sysname = null,
@COLUMN_NAME  sysname = null,
@TABLE_SCHEMA sysname = null
AS
BEGIN

/*************************************************************************************************

Name:
-----
DataDictionaryTableReport


Purpose:
--------
If you use SQL Server 2000's extended property features to document your database tables, this 
stored procedure can provide an easy way to extract the table and column-level descriptions for
reporting purposes.  This procedure can easily be modified to provide additional information to 
suite your needs.  Compile it to any SQL Server 2000 database.


Created By: 
-----------
Kevin O'Quinn, 2002-04-19
kevin_oquinn@hotmail.com
Copyright © 2002. All rights reserved.


Tested on: 
----------
SQL Server 2000;  will not work on SQL Server 6/7


Background:
-----------
Using SQL Server 2000's "extended property" features, it is easy to document your database objects
(i.e. create a data dictionary) directly inside the database itself.  Although this was available 
in previous version of SQL Server, with SQL Server 2000 we now have far more built-in support 
for actually maintaining these comments.  For example, you can simply open up a table in Enterprise 
Manager design mode, click on a column, and then enter your documentation comments for the column 
in the 'description' edit box in the bottom 'Columns' pane.  Comments can be made at the table-level 
by clicking on the 'Table and Index Properties' button and then entering the comments in the 
'description' box on the Table tab.  

You can use Query Analyzer's Object Browser to manage the extended properties of any object (stored 
procs, views, etc.)  You can also enter descriptions via the underlying system stored procedures 
sp_addExtendedProperty, sp_updateExtendedProperty and sp_dropExtendedProperty, and you can retrieve
this information via the system function fn_listExtendeProperty.

Additionally, it is worth noting that you can customize your extended properties to maintain 
multiple versions of your definitions.  For example, you can maintain a high-level business 
description (i.e. end-user / management), as well as a low-level technical description (i.e. for 
programmers / DBAs).  You can also use extended properties to store such things as input masks,
formatting rules, captions, etc.  However, this stored procedure assumes you are simply maintaining 
descriptions for a table and column-level data dictionary model using Enterprise Manager's built-in
interface.


Reference Materials:
--------------------
In addition to referrring to the SQL Server Books Online, the following articles provide good
information to help you understand how to use extended properties to document your database:
http://msdn.microsoft.com/library/en-us/createdb/cm_8_des_08_7qb7.asp
http://msdn.microsoft.com/library/en-us/dnsqlpro02/html/sql02a10.asp?frame=true
http://www.devx.com/premier/mgznarch/vbpj/2001/05may01/sq0501/sq0501.asp
http://www.sqlmag.com/Articles/Index.cfm?ArticleID=22399
http://www.sql-server-performance.com/ac_undocumented_2000_system_tables.asp


Usage of this procedure:
------------------------
First, use your preferred method to enter the extended property descriptions for your tables and 
their columns.  You can then use this stored procedure to produce a bulk report of all your
tables, their columns, and the descriptions entered for each.  You can also call this procedure
to produce a report for a single table, or even a single column on a table.

For example, you can use this procedure to produce a full listing of each table and its columns,
dump it into Excel, then use that as a fill-in-the-blank documentation template.  You could then
use a DTS package to pull the definitions out of the Excel spreadsheet and put them back into 
database's extended properties using the system functions.


Examples (using the PUBS database):
-------------------------------------------------------------
To see all tables and columns in the PUBS database:
EXEC DataDictionaryTableReport

To see all information on the Authors table:
EXEC DataDictionaryTableReport @TABLE_NAME='authors'

To see only information on the 'au_fname' column in the Authors table:
EXEC DataDictionaryTableReport @TABLE_NAME='authors', @COLUMN_NAME='au_fname'


Modification Log:
-----------------
2002-04-23 KOQUINN: fixed bug where Views showed in result set;  also added @TABLE_SCHEMA argument
so that we can view users other than previously hard-coded 'dbo'


*************************************************************************************************/
SET NOCOUNT ON


IF (@TABLE_SCHEMA IS NULL)
SET @TABLE_SCHEMA='dbo'


DECLARE @curTableName sysname -- cursor variable


-- create two table variables to store the result sets
-- Note: due to usage of the sql_variant data types, this will produce a warning about row size
--     upon compilations.  However, this can be ignored, as the condition will never occur.

-- @defnBuildTbl stores all existing extended properties meeting the criteria
DECLARE @defnBuildTbl TABLE (
tableName sysname NULL, 
objtype sysname NULL, 
objname sysname NULL, 
[name] sysname NULL, 
value sql_variant  NULL)

-- @defnResultsTbl stores the end result set
DECLARE @defResultsTbl TABLE (
TABLE_NAME sysname NULL, 
COLUMN_NAME sysname NULL, 
ORDINAL_POSITION smallint NULL,
[Description] sql_variant NULL,
DATA_TYPE nvarchar(256),
CHARACTER_MAXIMUM_LENGTH int, 
NUMERIC_PRECISION smallint, 
NUMERIC_SCALE int,
IS_NULLABLE varchar(3) )

-- cursor through the system table list of tables;
-- if a tableName argument is passed in, only use that, otherwise look up all tables
DECLARE cTables CURSOR FOR
SELECT TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLES  -- preferable to use these views rather than system tables
WHERE TABLE_SCHEMA=@TABLE_SCHEMA 
AND TABLE_TYPE='BASE TABLE'
AND TABLE_NAME=COALESCE(@TABLE_NAME, TABLE_NAME) 

OPEN cTables

FETCH NEXT FROM cTables
INTO @curTableName

WHILE @@FETCH_STATUS = 0
BEGIN

-- store listing of extended properties for this table, if any

INSERT @defnBuildTbl (tablename, objtype, objname, [name], value)
-- capture table comment, if any
SELECT @curTableName, *
FROM ::fn_listextendedproperty(default, 'user', @TABLE_SCHEMA, 'table', @curTableName, default, default)
WHERE name='MS_Description' -- exclude 'caption' types
UNION
-- capture comments for any columns in the table
SELECT @curTableName, *
FROM   ::fn_listextendedproperty (default, 'user', @TABLE_SCHEMA, 'table', @curTableName, 'column', default)
WHERE name='MS_Description' -- exclude 'caption' types

FETCH NEXT FROM cTables
INTO @curTableName
END

CLOSE cTables
DEALLOCATE cTables


-- now compile the result set.  We again select from the system tables to get the
-- full list of tables and columns, then do an outer join against our previous
-- result set.  This is because we may not have comments in ever table/column.
-- By doing this, we can generate a full template data dictionary that includes
-- any comments we have entered, and blanks for any incomplete documentation.


INSERT @defResultsTbl (TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION, [Description], DATA_TYPE, 
CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE, IS_NULLABLE )
SELECT t.TABLE_NAME, 
t.COLUMN_NAME, 
t.ORDINAL_POSITION,
[Description]=isNull(dt.value, dc.value),
t.DATA_TYPE, 
t.CHARACTER_MAXIMUM_LENGTH, 
t.NUMERIC_PRECISION, 
t.NUMERIC_SCALE,
t.IS_NULLABLE
FROM (-- generate a derived table that contains all tables and columns.
-- one row for each table-level entry...
SELECT 
TABLE_NAME, COLUMN_NAME=NULL, DATA_TYPE=NULL,
CHARACTER_MAXIMUM_LENGTH=NULL, NUMERIC_PRECISION=NULL, NUMERIC_SCALE=NULL,
IS_NULLABLE=NULL, ORDINAL_POSITION=0
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA=@TABLE_SCHEMA 
AND TABLE_TYPE='BASE TABLE'
UNION
-- ... and one row for each of the columns per table
SELECT c.TABLE_NAME, c.COLUMN_NAME, c.DATA_TYPE, 
c.CHARACTER_MAXIMUM_LENGTH, c.NUMERIC_PRECISION, c.NUMERIC_SCALE,
c.IS_NULLABLE, c.ORDINAL_POSITION
FROM INFORMATION_SCHEMA.TABLES AS t
JOIN INFORMATION_SCHEMA.COLUMNS AS c ON
t.TABLE_CATALOG = c.TABLE_CATALOG
AND t.TABLE_SCHEMA = c.TABLE_SCHEMA
AND t.TABLE_NAME = c.TABLE_NAME
WHERE t.TABLE_SCHEMA=@TABLE_SCHEMA 
AND t.TABLE_TYPE='BASE TABLE'
AND c.TABLE_SCHEMA=@TABLE_SCHEMA 
AND c.COLUMN_NAME=COALESCE(@COLUMN_NAME, COLUMN_NAME) ) AS t
LEFT OUTER JOIN @defnBuildTbl AS dt ON -- any table-level comments, if any
dt.tableName = t.TABLE_NAME
AND dt.objName = t.TABLE_NAME
AND dt.objType = 'TABLE'
AND t.COLUMN_NAME IS NULL
LEFT OUTER JOIN @defnBuildTbl AS dc ON -- any column-level comments, if any
dc.tableName = t.TABLE_NAME
AND dc.ObjName = t.COLUMN_NAME
AND dc.objType = 'COLUMN'
WHERE t.TABLE_NAME=COALESCE(@TABLE_NAME, TABLE_NAME) 


-- finally, return the result set;  comment out any columns you don't need/want
IF (@COLUMN_NAME IS NOT NULL) -- column-level
SELECT TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION, [Description], DATA_TYPE, 
CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE, IS_NULLABLE
FROM @defResultsTbl
WHERE COLUMN_NAME=@COLUMN_NAME

ELSE -- table-level
SELECT TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION, [Description], DATA_TYPE, 
CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE, IS_NULLABLE
FROM @defResultsTbl
ORDER BY TABLE_NAME, ORDINAL_POSITION

RETURN
End -- procedure
GO


GRANT ALL ON DataDictionaryTableReport TO public
GO