Create a Data Dictionary on the fly for any SQL Server 2005 or 2008 Database

I have included a quick script that creates a stored procedure that will generate a data dictionary on the fly for any SQL Server 2005 or 2008 database:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

-------------------------------------------------------------------------------
-- Date:   November 10, 2010
-- Author: Keith R. Davis
-- Desc:   Displays the data dictionary for the specified schema.
--
-- Param:  @SchemaName - name of the schema to document
--
-- Return: Nothing
-------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[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;

Executing the stored procedure, specifying the schema to document (in this case ‘meta’):

EXECUTE [dbo].[ShowDataDictionary] 'meta'

Will generate the following output (click to enlarge):

Please follow and like us:

6 Replies to “Create a Data Dictionary on the fly for any SQL Server 2005 or 2008 Database”

Leave a Reply

Your email address will not be published. Required fields are marked *