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