Generate a simple data dictionary for SQL Server; uses the Description extended property.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE PROCEDURE GenerateDataDictionary | |
@TableName NVARCHAR(MAX) | |
AS | |
BEGIN | |
SELECT | |
c.name AS ColumnName, | |
CASE | |
WHEN c.max_length = -1 THEN dt.name + '(max)' | |
WHEN dt.name = 'nvarchar' THEN dt.name + '('+CAST(ISNULL(c.max_length,0)/2 AS NVARCHAR)+')' | |
WHEN dt.name = 'varchar' THEN dt.name + '('+CAST(ISNULL(c.max_length,0) AS nvarchar)+')' | |
WHEN dt.name = 'nchar' THEN dt.name + '('+CAST(ISNULL(c.max_length,0)/2 AS NVARCHAR)+')' | |
WHEN dt.name = 'char' THEN dt.name + '('+CAST(ISNULL(c.max_length,0) AS nvarchar)+')' | |
ELSE dt.name | |
END AS DataType, | |
ep.value AS Description | |
FROM sys.all_columns c | |
RIGHT OUTER JOIN sys.tables t ON t.object_id = c.object_id | |
LEFT OUTER JOIN sys.types dt ON dt.system_type_id = c.system_type_id AND dt.name != 'sysname' | |
LEFT OUTER JOIN sys.extended_properties ep ON ep.major_id = t.object_id AND ep.minor_id = c.column_id AND ep.name = 'MS_Description' | |
WHERE t.name = @TableName | |
ORDER BY Column_Id | |
END |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment