Generate the data dictionary of SQL database Schema using SQL Query

What is Data Dictionary in SQL Server?

Data dictionary in SQL server is collection of explanation about tables, columns/fields, data type, length, constraints, relationship between tables and data model to understand the participated objects in the database.

Data dictionary is an important one to developers/DBA to understand the database objects,  attributes and identify the relationship between tables for their development. There are multiple ways to generate the data dictionary in SQL Server and this post explains how can we extract the data dictionary using SQL server system defined objects.

Sample Result:

How to generate the Data Dictionary for SQL database schema

Execute the below mentioned code on your database and it will generate the database data dictionary using system defined objects.

Data Dictionary Content:

Column Name	Description
Database Name	Database Name.
Schema	Schema Name.
Table Name	Table Name.
Column Name	Column Name.
DataType	Column's Data type. For instance CHAR, VARCHAR and etc.
Length	Column's Length.
Precision	Precision value of numeric field.
Scale	Scale value of numeric field.
IsNullable	Nullable Flag. 1 - Column is Nullable, 0 - Column is not Nullable.
IsPrimaryKey	Primary Key Flag. 1 - Column has Primary key Constraint, 0 - Normal Column.
Primary Key Constraint	Primary Key Constraint Name.
IsIndexed	Index Flag. 1 - Column is indexed. 0 - Not indexed.
IsIncludedIndex	Included Index Flag. 1 - Column is available in Included Index, 0 - Not Indexed.
Index Name	Index Name, if the column has index.
Foreign Key Constraint	Foreign Key Constraint Name. If the column has reference from other table.
Parent Table	Details about Parent table name if the column has Foreign key Constraint.
Default Constraint	Default Constraint Name if the column has Default Constraint.
Comments	Extended property value of column.

SQL Query:

SELECT  
   DB_NAME() AS [Database Name],
   OBJECT_SCHEMA_NAME(TBL.[object_id],DB_ID()) AS [Schema],   
   TBL.[name] AS [Table name], 
   AC.[name] AS [Column name],   
   UPPER(TY.[name]) AS DataType, 
   AC.[max_length] AS [Length],  
   AC.[precision], 
   AC.[scale], 
   AC.[is_nullable] AS IsNullable,
   ISNULL(SI.is_primary_key,0) AS IsPrimaryKey,
   SKC.name as [Primary Key Constarint],
   (CASE WHEN SIC.index_column_id > 0 THEN 1 ELSE 0 END) AS IsIndexed,
   ISNULL(is_included_column, 0) AS IsIncludedIndex,
   SI.name AS [Index Name],
   OBJECT_NAME(SFC.constraint_object_id) as [Foreign Key Constraint],
   OBJECT_NAME(SFC.referenced_object_id) as [Parent Table],
   SDC.name AS [Default Constraint],
   SEP.value AS Comments 
FROM sys.tables AS TBL
INNER JOIN sys.all_columns AC ON TBL.[object_id] = AC.[object_id]  
INNER JOIN sys.types TY ON AC.[system_type_id] = TY.[system_type_id] AND AC.[user_type_id] = TY.[user_type_id]   
LEFT JOIN sys.index_columns SIC on sic.object_id = TBL.object_id AND AC.column_id = SIC.column_id
LEFT JOIN sys.indexes SI on SI.object_id = TBL.object_id AND SIC.index_id = SI.index_id
LEFT JOIN sys.foreign_key_columns SFC on SFC.parent_object_id = TBL.object_id AND SFC.parent_column_id = AC.column_id
LEFT JOIN sys.key_constraints SKC on skc.parent_object_id = TBL.object_id AND SIC.index_column_id = SKC.unique_index_id
LEFT JOIN sys.default_constraints SDC on SDC.parent_column_id = AC.column_id
LEFT JOIN sys.extended_properties SEP on SEP.major_id = TBL.object_id AND SEP.minor_id = AC.column_id
ORDER BY TBL.[name], AC.[column_id]

Also see below articles

Monitoring and Performance Tuning Tips for SQL Server database
SQL Server/Developer Interview Questions and Answers for Experienced Professional