Automated SQL Server 2000 Data Dictionary

The two stored procedures below will help with automating the generation and maintenance of a database data dictionary.
If you provide a description for each database field in a SQL Database, these stored procedures can be used to extract that description information together with some other useful column details.
The first stored proc simply details the column information for a single table:
CREATE PROCEDURE uxp_Table_Data_Dictionary
@Table_Name varchar(50)
AS
— Extract the table data dictionary
SELECT

    TABLE_NAME,
ORDINAL_POSITION,
COLUMN_NAME,
CASE DATA_TYPE
WHEN ‘varchar’ THEN ‘varchar(‘+CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(20))+’)’
ELSE DATA_TYPE
END AS DATA_TYPE,
ISNULL(COLUMN_DEFAULT, ”) AS COLUMN_DEFAULT,
IS_NULLABLE,
value AS PROPERTY_VALUE

FROM
INFORMATION_SCHEMA.COLUMNS AS info
JOIN ::fn_listextendedproperty (NULL, ‘user’, ‘dbo’, ‘table’, @Table_Name, ‘column’, default) AS extended ON info.COLUMN_NAME = extended.objname

WHERE

    TABLE_NAME = @Table_Name
The second stored procedure extends this a little and reports the column information for all user tables within the current database:
CREATE PROCEDURE uxp_System_Data_Dictionary
AS
    — Create a cursor containing all user tables
    DECLARE @tableName varchar(40)
    DECLARE table_cursor CURSOR FOR
    SELECT name FROM sysobjects WHERE type = ‘U’ AND name <> ‘dtproperties’
    OPEN table_cursor
    — Perform the first fetch.
    FETCH NEXT FROM table_cursor INTO @tableName
    — Check @@FETCH_STATUS to see if there are any more rows to fetch.
    WHILE @@FETCH_STATUS = 0
    BEGIN
        — Execute the table data dictionary generator
        EXEC uxp_Table_Data_Dictionary @tableName
        — Fetch the next table
        FETCH NEXT FROM table_cursor INTO @tableName
END
    — Tidy Up
CLOSE table_cursor
DEALLOCATE table_cursor

Leave a Reply

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