SQL Server Data Dictionary Query - Data table

This query returns list of tables and their columns with details.

SELECT @@Servername AS ServerName     , DB_NAME() AS DatabaseName     , DB_ID() AS DatabaseId     , sc.name AS SchemaName     , tab.schema_id AS SchemaId     , tab.NAME AS TableName     , tab.object_id AS TableId     , col.NAME AS ColumnName     , col.column_id     , t.NAME AS DataTypeName     , CASE WHEN t.NAME IN('nchar', 'nvarchar') THEN col.max_length/2 ELSE col.max_length END AS Length_Size     , t.max_length     , CAST(col.is_identity AS INT) AS is_identity     , col.scale     , t.precision AS Precision     , t.NAME + CASE WHEN t.is_user_defined = 0 THEN ISNULL('(' + CASE WHEN t.NAME IN('binary', 'char', 'nchar', 'varchar', 'nvarchar', 'varbinary') THEN CASE col.max_length WHEN -1 THEN 'MAX' ELSE CASE WHEN t.NAME IN('nchar', 'nvarchar') THEN CAST(col.max_length / 2 AS VARCHAR(4)) ELSE CAST(col.max_length AS VARCHAR(4)) END END WHEN t.NAME IN('datetime2', 'datetimeoffset', 'time') THEN CAST(col.scale AS VARCHAR(4)) WHEN t.NAME IN('decimal', 'numeric') THEN CAST(col.precision AS VARCHAR(4)) + ', ' + CAST(col.scale AS VARCHAR(4)) END + ')', '') ELSE ':' +             (             SELECT c_t.NAME + ISNULL('(' + CASE WHEN c_t.NAME IN('binary', 'char', 'nchar', 'varchar', 'nvarchar', 'varbinary') THEN CASE c.max_length WHEN -1 THEN 'MAX' ELSE CASE WHEN t.NAME IN('nchar', 'nvarchar') THEN CAST(c.max_length / 2 AS VARCHAR(4)) ELSE CAST(c.max_length AS VARCHAR(4)) END END WHEN c_t.NAME IN('datetime2', 'datetimeoffset', 'time') THEN CAST(c.scale AS VARCHAR(4)) WHEN c_t.NAME IN('decimal', 'numeric') THEN CAST(c.precision AS VARCHAR(4)) + ', ' + CAST(c.scale AS VARCHAR(4)) END + ')', '')             FROM sys.columns AS c             INNER JOIN sys.types AS c_t ON c.system_type_id = c_t.user_type_id             WHERE                 c.object_id = col.object_id                 AND c.column_id = col.column_id                 AND c.user_type_id = col.user_type_id             ) END AS [DataType]     , CASE WHEN col.collation_name IS NOT NULL THEN 1 ELSE 0 END has_collation_name     , col.collation_name     , col.is_nullable     , CASE WHEN col.is_nullable = 0 THEN 'N' ELSE 'Y' END AS nullable     , CASE WHEN def.DEFINITION IS NOT NULL THEN 1 ELSE 0 END AS has_default_value     , CASE WHEN def.DEFINITION IS NOT NULL THEN def.DEFINITION ELSE '' END AS default_value     , CASE WHEN pk.column_id IS NOT NULL THEN 1 ELSE 0 END AS is_primary_key     , CASE WHEN pk.column_id IS NOT NULL THEN 'PK' ELSE '' END AS primary_key     , CASE WHEN fk.parent_column_id IS NOT NULL THEN 1 ELSE 0 END AS is_foreign_key     , CASE WHEN fk.parent_column_id IS NOT NULL THEN 'FK' ELSE '' END AS foreign_key     , CASE WHEN uk.column_id IS NOT NULL THEN 1 ELSE 0 END AS is_unique_key     , CASE WHEN uk.column_id IS NOT NULL THEN 'UK' ELSE '' END AS unique_key     , CASE WHEN ch.check_const IS NOT NULL THEN 1 ELSE 0 END AS is_check_contraint     , CASE WHEN ch.check_const IS NOT NULL THEN ch.check_const ELSE '' END AS check_contraint     , col.is_computed     , cc.DEFINITION AS computed_column_definition     , ep.value AS comments
FROM sys.computed_columns AS cc
RIGHT OUTER JOIN sys.tables AS tab
INNER JOIN sys.schemas AS sc ON tab.schema_id = sc.schema_id
LEFT OUTER JOIN sys.columns AS col ON tab.object_id = col.object_id
LEFT OUTER JOIN sys.types AS t ON col.user_type_id = t.user_type_id
LEFT OUTER JOIN sys.default_constraints AS def ON def.object_id = col.default_object_id
LEFT OUTER JOIN (SELECT sys.index_columns.object_id     , sys.index_columns.column_id
FROM sys.index_columns
INNER JOIN sys.indexes ON sys.index_columns.object_id = sys.indexes.object_id
AND sys.index_columns.index_id = sys.indexes.index_id
WHERE     (sys.indexes.is_primary_key = 1)) AS pk ON col.object_id = pk.object_id
AND col.column_id = pk.column_id
LEFT OUTER JOIN (SELECT fc.parent_column_id     , fc.parent_object_id
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc ON f.object_id = fc.constraint_object_id
GROUP BY fc.parent_column_id , fc.parent_object_id) AS fk ON fk.parent_object_id = col.object_id
AND fk.parent_column_id = col.column_id
LEFT OUTER JOIN (
SELECT parent_column_id     , parent_object_id     , 'Check' AS check_const
FROM sys.check_constraints AS c
GROUP BY parent_column_id , parent_object_id
) AS ch ON col.column_id = ch.parent_column_id
AND col.object_id = ch.parent_object_id
LEFT OUTER JOIN (
SELECT index_columns_1.object_id     , index_columns_1.column_id
FROM sys.index_columns AS index_columns_1
INNER JOIN sys.indexes AS indexes_1 ON indexes_1.index_id = index_columns_1.index_id
AND indexes_1.object_id = index_columns_1.object_id
WHERE     (indexes_1.is_unique_constraint = 1)
GROUP BY index_columns_1.object_id , index_columns_1.column_id
) AS uk ON col.column_id = uk.column_id
AND col.object_id = uk.object_id
LEFT OUTER JOIN sys.extended_properties AS ep ON tab.object_id = ep.major_id
AND col.column_id = ep.minor_id
AND ep.name = 'MS_Description'
AND ep.class_desc = 'OBJECT_OR_COLUMN' ON cc.object_id = tab.object_id
AND cc.column_id = col.column_id

sources : https://dataedo.com/blog/useful-sql-server-data-dictionary-queries-every-dba-should-have