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

Rate

5 (1)

Share

Share

Rate

5 (1)