Skip to content

Instantly share code, notes, and snippets.

@brentmaxwell
Created September 9, 2019 16:17
Show Gist options
  • Save brentmaxwell/470d53f7648e92130ddffea46ae61d49 to your computer and use it in GitHub Desktop.
Save brentmaxwell/470d53f7648e92130ddffea46ae61d49 to your computer and use it in GitHub Desktop.
Generate a simple data dictionary for SQL Server; uses the Description extended property.
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