Skip to content

Instantly share code, notes, and snippets.

@petergi

petergi/Generate a Data Dictionary for an MS SQL Database.sql

Last active July 8, 2025 16:44
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save petergi/11cffa97eaf17fe4d9278b28cf809e7f to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save petergi/11cffa97eaf17fe4d9278b28cf809e7f to your computer and use it in GitHub Desktop.
Download ZIP
After running these queries, you'll have all the information needed to create a comprehensive data dictionary document for your  database.
Raw
Generate a Data Dictionary for an MS SQL Database.sql
-- Data Dictionary Generation Queries
-- Run these queries sequentially to build your data dictionary
-- Data Dictionary Generation Queries
-- Run these queries sequentially to build your data dictionary
-- Each query provides different aspects of your database structure:
-- 1.Tables Overview - Lists all tables with descriptions
-- 2.Column Details - Complete column information including data types, nullability, and defaults
-- 3.Primary Keys - Identifies primary key constraints
-- 4.Foreign Keys - Shows table relationships
-- 5.Indexes - Lists all indexes for performance optimization
-- 6.Check Constraints - Shows data validation rules
-- 1. Get all tables and their descriptions
SELECT
t.TABLE_SCHEMA as [Schema],
t.TABLE_NAME as [Table Name],
t.TABLE_TYPE as [Table Type],
ep.value as [Description]
FROM INFORMATION_SCHEMA.TABLES t
LEFT JOIN sys.tables st ON t.TABLE_NAME = st.name
LEFT JOIN sys.extended_properties ep ON st.object_id = ep.major_id
AND ep.minor_id =
AND ep.name = 'MS_Description'
WHERE t.TABLE_TYPE = 'BASE TABLE'
ORDER BY t.TABLE_SCHEMA, t.TABLE_NAME;
-- 2. Get detailed column information
SELECT
t.TABLE_SCHEMA as [Schema],
t.TABLE_NAME as [Table Name],
c.COLUMN_NAME as [Column Name],
c.ORDINAL_POSITION as [Position],
c.DATA_TYPE as [Data Type],
CASE
WHEN c.CHARACTER_MAXIMUM_LENGTH IS NOT NULL
THEN c.DATA_TYPE + '(' + CAST(c.CHARACTER_MAXIMUM_LENGTH AS VARCHAR) + ')'
WHEN c.NUMERIC_PRECISION IS NOT NULL AND c.NUMERIC_SCALE IS NOT NULL
THEN c.DATA_TYPE + '(' + CAST(c.NUMERIC_PRECISION AS VARCHAR) + ',' + CAST(c.NUMERIC_SCALE AS VARCHAR) + ')'
ELSE c.DATA_TYPE
END as [Full Data Type],
c.IS_NULLABLE as [Nullable],
c.COLUMN_DEFAULT as [Default Value],
ep.value as [Description]
FROM INFORMATION_SCHEMA.TABLES t
INNER JOIN INFORMATION_SCHEMA.COLUMNS c ON t.TABLE_NAME = c.TABLE_NAME AND t.TABLE_SCHEMA = c.TABLE_SCHEMA
LEFT JOIN sys.tables st ON t.TABLE_NAME = st.name
LEFT JOIN sys.columns sc ON st.object_id = sc.object_id AND c.COLUMN_NAME = sc.name
LEFT JOIN sys.extended_properties ep ON st.object_id = ep.major_id
AND sc.column_id = ep.minor_id
AND ep.name = 'MS_Description'
WHERE t.TABLE_TYPE = 'BASE TABLE'
ORDER BY t.TABLE_SCHEMA, t.TABLE_NAME, c.ORDINAL_POSITION;
-- 3. Get Primary Keys
SELECT
tc.TABLE_SCHEMA as [Schema],
tc.TABLE_NAME as [Table Name],
tc.CONSTRAINT_NAME as [Constraint Name],
kcu.COLUMN_NAME as [Column Name],
kcu.ORDINAL_POSITION as [Key Position]
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
ON tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
WHERE tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
ORDER BY tc.TABLE_SCHEMA, tc.TABLE_NAME, kcu.ORDINAL_POSITION;
-- 4. Get Foreign Keys and Relationships
SELECT
fk.TABLE_SCHEMA as [Schema],
fk.TABLE_NAME as [Table Name],
fk.COLUMN_NAME as [Foreign Key Column],
fk.CONSTRAINT_NAME as [FK Constraint Name],
pk.TABLE_SCHEMA as [Referenced Schema],
pk.TABLE_NAME as [Referenced Table],
pk.COLUMN_NAME as [Referenced Column]
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE fk
ON rc.CONSTRAINT_NAME = fk.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE pk
ON rc.UNIQUE_CONSTRAINT_NAME = pk.CONSTRAINT_NAME
ORDER BY fk.TABLE_SCHEMA, fk.TABLE_NAME, fk.COLUMN_NAME;
-- 5. Get Indexes
SELECT
s.name as [Schema],
t.name as [Table Name],
i.name as [Index Name],
i.type_desc as [Index Type],
i.is_unique as [Is Unique],
i.is_primary_key as [Is Primary Key],
STRING_AGG(c.name, ', ') WITHIN GROUP (ORDER BY ic.key_ordinal) as [Columns]
FROM sys.indexes i
INNER JOIN sys.tables t ON i.object_id = t.object_id
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
INNER JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
INNER JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE i.type > 0 -- Exclude heaps
GROUP BY s.name, t.name, i.name, i.type_desc, i.is_unique, i.is_primary_key
ORDER BY s.name, t.name, i.name;
-- 6. Get Check Constraints
SELECT
tc.TABLE_SCHEMA as [Schema],
tc.TABLE_NAME as [Table Name],
tc.CONSTRAINT_NAME as [Constraint Name],
cc.CHECK_CLAUSE as [Check Clause]
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
INNER JOIN INFORMATION_SCHEMA.CHECK_CONSTRAINTS cc
ON tc.CONSTRAINT_NAME = cc.CONSTRAINT_NAME
WHERE tc.CONSTRAINT_TYPE = 'CHECK'
ORDER BY tc.TABLE_SCHEMA, tc.TABLE_NAME, tc.CONSTRAINT_NAME;
-- 1. Get all tables and their descriptions
SELECT
t.TABLE_SCHEMA as [Schema],
t.TABLE_NAME as [Table Name],
t.TABLE_TYPE as [Table Type],
ep.value as [Description]
FROM INFORMATION_SCHEMA.TABLES t
LEFT JOIN sys.tables st ON t.TABLE_NAME = st.name
LEFT JOIN sys.extended_properties ep ON st.object_id = ep.major_id
AND ep.minor_id =
AND ep.name = 'MS_Description'
WHERE t.TABLE_TYPE = 'BASE TABLE'
ORDER BY t.TABLE_SCHEMA, t.TABLE_NAME;
-- 2. Get detailed column information
SELECT
t.TABLE_SCHEMA as [Schema],
t.TABLE_NAME as [Table Name],
c.COLUMN_NAME as [Column Name],
c.ORDINAL_POSITION as [Position],
c.DATA_TYPE as [Data Type],
CASE
WHEN c.CHARACTER_MAXIMUM_LENGTH IS NOT NULL
THEN c.DATA_TYPE + '(' + CAST(c.CHARACTER_MAXIMUM_LENGTH AS VARCHAR) + ')'
WHEN c.NUMERIC_PRECISION IS NOT NULL AND c.NUMERIC_SCALE IS NOT NULL
THEN c.DATA_TYPE + '(' + CAST(c.NUMERIC_PRECISION AS VARCHAR) + ',' + CAST(c.NUMERIC_SCALE AS VARCHAR) + ')'
ELSE c.DATA_TYPE
END as [Full Data Type],
c.IS_NULLABLE as [Nullable],
c.COLUMN_DEFAULT as [Default Value],
ep.value as [Description]
FROM INFORMATION_SCHEMA.TABLES t
INNER JOIN INFORMATION_SCHEMA.COLUMNS c ON t.TABLE_NAME = c.TABLE_NAME AND t.TABLE_SCHEMA = c.TABLE_SCHEMA
LEFT JOIN sys.tables st ON t.TABLE_NAME = st.name
LEFT JOIN sys.columns sc ON st.object_id = sc.object_id AND c.COLUMN_NAME = sc.name
LEFT JOIN sys.extended_properties ep ON st.object_id = ep.major_id
AND sc.column_id = ep.minor_id
AND ep.name = 'MS_Description'
WHERE t.TABLE_TYPE = 'BASE TABLE'
ORDER BY t.TABLE_SCHEMA, t.TABLE_NAME, c.ORDINAL_POSITION;
-- 3. Get Primary Keys
SELECT
tc.TABLE_SCHEMA as [Schema],
tc.TABLE_NAME as [Table Name],
tc.CONSTRAINT_NAME as [Constraint Name],
kcu.COLUMN_NAME as [Column Name],
kcu.ORDINAL_POSITION as [Key Position]
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
ON tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
WHERE tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
ORDER BY tc.TABLE_SCHEMA, tc.TABLE_NAME, kcu.ORDINAL_POSITION;
-- 4. Get Foreign Keys and Relationships
SELECT
fk.TABLE_SCHEMA as [Schema],
fk.TABLE_NAME as [Table Name],
fk.COLUMN_NAME as [Foreign Key Column],
fk.CONSTRAINT_NAME as [FK Constraint Name],
pk.TABLE_SCHEMA as [Referenced Schema],
pk.TABLE_NAME as [Referenced Table],
pk.COLUMN_NAME as [Referenced Column]
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE fk
ON rc.CONSTRAINT_NAME = fk.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE pk
ON rc.UNIQUE_CONSTRAINT_NAME = pk.CONSTRAINT_NAME
ORDER BY fk.TABLE_SCHEMA, fk.TABLE_NAME, fk.COLUMN_NAME;
-- 5. Get Indexes
SELECT
s.name as [Schema],
t.name as [Table Name],
i.name as [Index Name],
i.type_desc as [Index Type],
i.is_unique as [Is Unique],
i.is_primary_key as [Is Primary Key],
STRING_AGG(c.name, ', ') WITHIN GROUP (ORDER BY ic.key_ordinal) as [Columns]
FROM sys.indexes i
INNER JOIN sys.tables t ON i.object_id = t.object_id
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
INNER JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
INNER JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE i.type > 0 -- Exclude heaps
GROUP BY s.name, t.name, i.name, i.type_desc, i.is_unique, i.is_primary_key
ORDER BY s.name, t.name, i.name;
-- 6. Get Check Constraints
SELECT
tc.TABLE_SCHEMA as [Schema],
tc.TABLE_NAME as [Table Name],
tc.CONSTRAINT_NAME as [Constraint Name],
cc.CHECK_CLAUSE as [Check Clause]
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
INNER JOIN INFORMATION_SCHEMA.CHECK_CONSTRAINTS cc
ON tc.CONSTRAINT_NAME = cc.CONSTRAINT_NAME
WHERE tc.CONSTRAINT_TYPE = 'CHECK'
ORDER BY tc.TABLE_SCHEMA, tc.TABLE_NAME, tc.CONSTRAINT_NAME;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment