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