SQL Server queries for data dictionary

List tables with schema, modification date, number of rows and columns, comments:

SELECT
	 t.object_id
	,s.name as schema_name
	,t.name as table_name
	,t.create_date as create_date 
	,t.modify_date as modify_date
	,p.nb_rows
	,c.nb_columns
	,CONVERT(nvarchar(max), ep.comments ) as comments
FROM sys.tables t
JOIN sys.schemas s ON t.schema_id = s.schema_id
JOIN (
	SELECT object_id, SUM(rows) as nb_rows, COUNT(*) as nb_pages
	FROM sys.partitions p 
	GROUP BY object_id
) p
ON t.object_id = p.object_id
JOIN (
	SELECT object_id, COUNT(*) as nb_columns FROM sys.columns GROUP BY object_id
) c
ON t.object_id = c.object_id
LEFT JOIN (
	SELECT major_id, value as comments
	FROM sys.extended_properties
	WHERE name = 'MS_Description'
	AND minor_id = 0
	AND class_desc = 'OBJECT_OR_COLUMN'
) ep 
ON t.object_id = ep.major_id
ORDER BY s.name, t.name

Something very similar can be done for views (without the number of rows):

SELECT
	 t.object_id
	,s.name as schema_name
	,t.name as view_name
	,t.create_date as create_date 
	,t.modify_date as modify_date
	,c.nb_columns
	,CONVERT(nvarchar(max), ep.comments ) as comments
FROM sys.views t
JOIN sys.schemas s ON t.schema_id = s.schema_id
JOIN (
	SELECT object_id, COUNT(*) as nb_columns FROM sys.columns GROUP BY object_id
) c
ON t.object_id = c.object_id
LEFT JOIN (
	SELECT major_id, value as comments
	FROM sys.extended_properties
	WHERE name = 'MS_Description'
	AND minor_id = 0
	AND class_desc = 'OBJECT_OR_COLUMN'
) ep 
ON t.object_id = ep.major_id
ORDER BY s.name, t.name

SELECT 
	 t.object_id as table_object_id
	,s.name as schema_name
	,t.name as table_name
	,c.name as column_name
	,c.column_id
	,ty.name as data_type
	,ty.name + COALESCE('(' +
		CASE WHEN ty.name IN('binary', 'char', 'varchar', 'varbinary') THEN 
				CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CONVERT(nvarchar(10), c.max_length) END
			WHEN ty.name IN('nchar', 'nvarchar') THEN 
				CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CONVERT(nvarchar(10), c.max_length/2) END
			WHEN ty.name IN('decimal', 'numeric') THEN CONVERT(nvarchar(10), c.precision) + ',' + CONVERT(nvarchar(10), c.scale)
			WHEN ty.name in ('datetime2', 'datetimeoffset', 'time') then CONVERT(nvarchar(10),c.scale )
			ELSE NULL
		END + ')',
		'') as data_type_ext
	,case when c.is_nullable = 0 then 'N' 
		else 'Y' end as nullable
	,case when def.definition is not null then def.definition 
		else '' end as default_value
	,COALESCE(puk.primary_key, '') as primary_key
	,case when fk.parent_column_id is not null then 'FK' 
		else '' end as foreign_key
	,COALESCE(puk.unique_key, '') as unique_key
	,case when ch.check_const is not null then ch.check_const 
		else '' end as check_contraint
	,cc.definition as computed_column_definition
	,ep.comments


FROM sys.tables t

JOIN sys.schemas s 
	ON t.schema_id = s.schema_id

JOIN sys.columns c 
	ON t.object_id = c.object_id

JOIN sys.types ty 
	ON c.user_type_id = ty.user_type_id

LEFT JOIN sys.default_constraints as def 
	ON def.object_id = c.default_object_id

LEFT JOIN (
	SELECT ic.object_id, ic.column_id
		,CASE WHEN ix.is_primary_key = 1 THEN 'PK' ELSE '' END as primary_key
		,CASE WHEN ix.is_unique_constraint = 1 THEN 'UK' ELSE '' END as unique_key
	FROM sys.index_columns ic
	JOIN sys.indexes ix
		ON ic.object_id = ix.object_id
		AND ic.index_id = ix.index_id
	WHERE ix.is_primary_key = 1 
	OR ix.is_unique_constraint = 1
) as puk 
	ON c.object_id = puk.object_id 
	AND c.column_id = puk.column_id

LEFT JOIN (
	SELECT fc.parent_column_id, fc.parent_object_id
	FROM sys.foreign_keys as f 
	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 = c.object_id 
	AND fk.parent_column_id = c.column_id 

LEFT JOIN (
	SELECT c.parent_column_id, c.parent_object_id, 'Check' check_const
	FROM sys.check_constraints as c
	GROUP BY c.parent_column_id, c.parent_object_id
) as ch
	ON c.column_id = ch.parent_column_id
	AND c.object_id = ch.parent_object_id

LEFT JOIN (
	SELECT major_id, minor_id, CONVERT(nvarchar(max), value) as comments
	FROM sys.extended_properties
	WHERE name = 'MS_Description'
	AND class_desc = 'OBJECT_OR_COLUMN'
) as ep 
	ON t.object_id = ep.major_id
	AND c.column_id = ep.minor_id

LEFT JOIN sys.computed_columns as cc
	ON t.object_id = cc.object_id
	AND c.column_id = cc.column_id

order by s.name, t.name, c.column_id;

SELECT 

	 t.object_id as view_object_id
	,s.name as schema_name
	,t.name as view_name
	,c.name as column_name
	,c.column_id
	,ty.name as data_type
	,ty.name + COALESCE('(' +
		CASE WHEN ty.name IN('binary', 'char', 'varchar', 'varbinary') THEN 
				CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CONVERT(nvarchar(10), c.max_length) END
			WHEN ty.name IN('nchar', 'nvarchar') THEN 
				CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CONVERT(nvarchar(10), c.max_length/2) END
			WHEN ty.name IN('decimal', 'numeric') THEN CONVERT(nvarchar(10), c.precision) + ',' + CONVERT(nvarchar(10), c.scale)
			WHEN ty.name in ('datetime2', 'datetimeoffset', 'time') then CONVERT(nvarchar(10),c.scale )
			ELSE NULL
		END + ')',
		'') as data_type_ext
	,case when c.is_nullable = 0 then 'N' 
		else 'Y' end as nullable
	,ep.comments


FROM sys.views t

JOIN sys.schemas s 
	ON t.schema_id = s.schema_id

JOIN sys.columns c 
	ON t.object_id = c.object_id

JOIN sys.types ty 
	ON c.user_type_id = ty.user_type_id

LEFT JOIN (
	SELECT major_id, minor_id, CONVERT(nvarchar(max), value) as comments
	FROM sys.extended_properties
	WHERE name = 'MS_Description'
	AND class_desc = 'OBJECT_OR_COLUMN'
) as ep 
	ON t.object_id = ep.major_id
	AND c.column_id = ep.minor_id

order by s.name, t.name, c.column_id;