Data Dictionary - SQL 2005
I know there are several scripts out there showing you on how to create a data dictionary... I created this script for creating a data dictionary for SQL 2005. It stores the data into a table in the database that you choose. The script only stores the table name, column name, default value of the column, data type and the description. However, if the description is empty or null, there will be a default entry of 'Description not defined' in the description field in the table. 09/27/2006: - I just added a line that will handle any single quotes being used in the description to allow the description to be entered, otherwise the script will fail
SQL Server 2005 ONLY
/*** Change to database that you want to create table in ***/USE [AdventureWorks]
GO
/**** Create Schema that is going to be used ****/CREATE SCHEMA [Data] AUTHORIZATION [dbo]
GO
/****** Create table that will hold the data dictionary information [Data].[Dictionary] ******/SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [Data].[Dictionary](
[ID] [int] IDENTITY(1,1) NOT NULL,
[TableName] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ColumnName] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ColumnDefaultValue] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DataType] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[IsNullable] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Description] [varchar](400) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_Dictionary] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/*** This will populate the "Data Dictionary table ****/DECLARE @Schema VARCHAR(100),
@TableName VARCHAR(150),
@ColumnName VARCHAR(150),
@ColumnDefaultValue VARCHAR(100),
@Null VARCHAR(100),
@Data VARCHAR(100),
@Description VARCHAR(1000),
@SQL NVARCHAR(200),
@SQL2 NVARCHAR(2000)
DECLARE TableName CURSOR FOR
SELECT IST.Table_Schema, IST.Table_Name, ISC.Column_Name,
CASE WHEN Column_Default IS NULL THEN 'Not Defined' ELSE Column_Default END Column_Default, Is_Nullable, CAST(Data_Type AS VARCHAR(100)) FROM INFORMATION_SCHEMA.TABLES IST
INNER JOIN INFORMATION_SCHEMA.COLUMNS ISC ON (ISC.Table_Name = IST.Table_Name)
WHERE IST.table_schema NOT IN('dbo', 'Reporting')
AND IST.TABLE_TYPE NOT IN ('View')
ORDER BY IST.TABLE_SCHEMA, IST.TABLE_NAME
OPEN TableName
FETCH NEXT FROM TableName
INTO @Schema, @TableName, @ColumnName, @ColumnDefaultValue, @Null, @Data
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE ColumnDescription CURSOR FOR
SELECT CAST(Value AS VARCHAR(1000))
FROM fn_listextendedproperty
(NULL, 'schema',''+ @Schema + '', 'table', ''+ @TableName +'','column', ''+ @ColumnName +'')
WHILE @@FETCH_STATUS = 0
BEGIN
OPEN ColumnDescription
FETCH NEXT FROM ColumnDescription
INTO @Description
SET @Description = CASE WHEN @Description = NULL OR @Description = '' THEN 'Description not defined' ELSE @Description END
/*** The line below adds an additional single quote allowing description to be entered if a single quote is being used in the description ***/
SET @Description = REPLACE(@Description,char(39),char(39)+char(39))
SET @SQL = 'INSERT INTO Data.Dictionary (SchemaName, TableName, ColumnName, ColumnDefaultValue, DataType, IsNullable, Description)'
SET @SQL2 = @SQL + ' VALUES (''' + @Schema + ''',''' + @TableName +''',''' + @ColumnName +''','''+ @ColumnDefaultValue +''','''+ @Data +''','''+ @Null +''','''+ @Description +''')'
EXEC SP_EXECUTESQL @SQL2
SET @Description = ''
FETCH NEXT FROM ColumnDescription
INTO @Description
END
CLOSE ColumnDescription
DEALLOCATE ColumnDescription
SET @Schema = ''
SET @TableName = ''
SET @ColumnName = ''
SET @ColumnDefaultValue = ''
SET @Null = ''
SET @Data = ''
FETCH NEXT FROM TableName
INTO @Schema, @TableName, @ColumnName, @ColumnDefaultValue, @Null, @Data
END
CLOSE TableName
DEALLOCATE TableName
GO
GRANT ALL ON [Data].[Dictionary] TO public 
