Data Dictionary for SQL Server 2008

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

-------------------------------------------------------------------------------
-- Date: May 10, 2012
-- Author: Shivkumar DESHMUKH
-- Desc: Displays the data dictionary for the specified schema. Provide the name
-- of Schema as input to the Stored Procedure
-- Param: @SchemaName - name of the schema to document
--
-- Return: Nothing
-------------------------------------------------------------------------------
-- exec ShowDataDictionary 'pwdtoll'

CREATE PROCEDURE [pwdtoll].[ShowDataDictionary](
@SchemaName VARCHAR(255)
)
AS

BEGIN
DECLARE @DataDictionary TABLE(
RowID [INT] IDENTITY(1,1) PRIMARY KEY,
TableName [VARCHAR](200),
ColumnName [VARCHAR](200),
OrdinalPosition INT,
ColumnDefault [VARCHAR](200),
IsNullable [BIT],
DataType [VARCHAR](20),
CharacterMaximumLength INT,
IsIndexed [BIT],
IsIdentity [VARCHAR](5),
IsPrimaryKey [BIT],
IsForeignKey [BIT],
ReferencedTableName [VARCHAR](200),
ReferencedColumnName [VARCHAR](200),
ObjectDescription [VARCHAR](500)
)

DECLARE @TableList TABLE(
RowID [INT] IDENTITY(1,1) PRIMARY KEY,
TableCatalog [VARCHAR](200),
TableSchema [VARCHAR](200),
TableName [VARCHAR](200),
TableType [VARCHAR](20),
ObjectDescription [VARCHAR](500)
)

DECLARE
@RowNumber INT,
@RowCount INT,
@TableName VARCHAR(255),
@ObjectDescription VARCHAR(500);

-- Create a temp table to hold the primary keys
-- can't do INSERT EXEC with a table variable
IF OBJECT_ID('tempdb..#PkColumns') IS not null
BEGIN
DROP TABLE #PkColumns
END

CREATE TABLE #PkColumns (
RowID [INT] IDENTITY(1,1) PRIMARY KEY,
table_Qualifier VARCHAR(255) NOT NULL,
owner_name VARCHAR(255) NOT NULL,
table_name VARCHAR(255) NOT NULL,
column_name VARCHAR(255) NOT NULL,
key_seq VARCHAR(255) NOT NULL,
Pk_Name VARCHAR(255) NOT NULL
)

-- Load the table list
INSERT @TableList (
TableCatalog,
TableSchema,
TableName,
TableType,
ObjectDescription)
SELECT
t.Table_Catalog,
t.Table_Schema,
t.Table_Name,
t.Table_Type,
CAST(ISNULL(e.VALUE,'') AS VARCHAR(500))
FROM
INFORMATION_SCHEMA.TABLES t
LEFT JOIN ::FN_LISTEXTENDEDPROPERTY (
NULL,
'schema',
@SchemaName,
'table',
@TableName,
NULL,
NULL
) e
ON t.Table_Name = e.objname COLLATE Latin1_General_CI_AS
WHERE
t.Table_Type = 'BASE TABLE' AND t.Table_Schema = @SchemaName

SELECT @RowCount = COUNT(*) FROM @TableList

-- Loop through the tables
SET @RowNumber = 1

WHILE @RowNumber <= @RowCount
BEGIN
SELECT
@TableName = TableName,
@ObjectDescription = ObjectDescription
FROM @TableList
WHERE RowID = @RowNumber

-- Clear primary keys table
DELETE #PkColumns

-- Insert the primary key records retrieved by the system stored procedure
INSERT #PkColumns
EXEC SP_PKEYS @table_name= @TableName, @table_owner = @SchemaName

-- Insert the table name and description for header purposes
INSERT @DataDictionary(
TableName,
ColumnName,
OrdinalPosition,
ColumnDefault,
IsNullable,
DataType,
CharacterMaximumLength,
IsIdentity,
IsPrimaryKey,
IsForeignKey,
ReferencedTableName,
ReferencedColumnName,
ObjectDescription
) VALUES (
@TableName,
'',
0,
'',
0,
'',
0,
0,
0,
0,
'',
'',
@ObjectDescription
)

-- Insert the column schema information
INSERT @DataDictionary (
TableName,
ColumnName,
OrdinalPosition,
ColumnDefault,
IsNullable,
DataType,
CharacterMaximumLength,
IsIndexed,
IsIdentity,
IsPrimaryKey,
IsForeignKey,
ReferencedTableName,
ReferencedColumnName,
ObjectDescription
)
SELECT
t.table_name,
c.column_name,
c.ordinal_position,
ISNULL(c.column_default, ''),
CASE WHEN c.is_nullable = 'Yes' THEN 1 ELSE 0 END,
c.data_type,
ISNULL(c.character_maximum_length, ''),
(
SELECT CASE WHEN COUNT(sys.columns.name) = 0 OR COUNT(sys.columns.name) IS NULL THEN 0 ELSE 1 END
FROM sys.indexes, sys.index_columns, sys.columns
WHERE sys.indexes.OBJECT_ID = sys.index_columns.OBJECT_ID AND
sys.index_columns.OBJECT_ID = sys.columns.OBJECT_ID AND
sys.index_columns.column_id = sys.columns.column_id AND
sys.indexes.OBJECT_ID=OBJECT_ID(@SchemaName + '.' + t.table_name) AND
sys.columns.name = c.column_name
) AS IsIndexed,
(SELECT COLUMNPROPERTY (
OBJECT_ID(@SchemaName + '.' + t.table_name),
c.Column_Name,'IsIdentity')
) AS IsIdentity,
CASE WHEN pk.column_name IS NULL THEN 0
ELSE 1
END AS IsPrimaryKey,
CASE WHEN Fkey.REFERENCED_TABLE_NAME IS NULL THEN 0
ELSE 1
END AS IsForeignKey,
ISNULL(Fkey.REFERENCED_TABLE_NAME, ''),
ISNULL(Fkey.Referenced_Column_Name, ''),
CAST(ISNULL(e.VALUE,'') AS VARCHAR(500)) AS 'ColumnDescription'
FROM
information_schema.tables t
INNER JOIN information_schema.columns C
ON t.table_name = c.table_name
LEFT OUTER JOIN [#PkColumns] Pk
ON PK.column_Name = c.column_Name
LEFT OUTER JOIN (
SELECT
CASE WHEN OBJECTPROPERTY(CONSTID, 'CNSTISDISABLED') = 0
THEN 'Enabled'
ELSE 'Disabled' END AS Status,
OBJECT_NAME(CONSTID) AS Constraint_Name,
OBJECT_NAME(FKEYID) AS Table_Name,
COL_NAME(FKEYID, FKEY) AS Column_Name,
OBJECT_NAME(RKEYID) AS Referenced_Table_Name,
COL_NAME(RKEYID, RKEY) AS Referenced_Column_Name
FROM SYSFOREIGNKEYS
) AS Fkey
ON c.table_name = Fkey.table_name AND c.column_Name = Fkey.Column_Name
LEFT JOIN ::FN_LISTEXTENDEDPROPERTY (
NULL,
'schema',
@SchemaName,
'table',
@TableName,
'column',
default
) e
ON c.Column_Name = e.objname COLLATE Latin1_General_CI_AS
WHERE t.table_name = @TableName
ORDER BY c.ordinal_position

SET @RowNumber = @RowNumber + 1
END

-- Drop the temp table
DROP TABLE #PkColumns

-- Return the data dictionary
SELECT * FROM @DataDictionary d ORDER BY d.TableName, d.OrdinalPosition
END;