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 *