Code for generating data dictionaries for Databases with and without shcemas

Data Dict.sql

-----------------------------------------
-- simple database 
-----------------------------------------

SELECT CONVERT(VARCHAR(140),s.name) AS [Schema Name],
    CONVERT(VARCHAR(140),o.name) AS [Object Name],
    CONVERT(VARCHAR(140),c.name) AS [Field Name],
    CONVERT(VARCHAR,t.name) AS [Field Type] ,
    CONVERT( VARCHAR, CASE 
        WHEN o.type = 'U' THEN 'Table' 
        WHEN o.type = 'V' THEN 'View' 
        ELSE o.type 
    END) AS [Entity Type],
    ISNULL(i.is_primary_key, 0) AS is_primary_key,
    c.max_length AS [Field Length],
    c.precision AS [Precision],
    p.rows
FROM sys.objects o
    LEFT JOIN sys.schemas s ON s.schema_id = o.schema_id
    INNER JOIN sys.columns c ON o.object_id = c.object_id
    INNER JOIN systypes t ON c.system_type_id = t.xtype
    LEFT JOIN sys.partitions p ON o.object_id=p.object_id
    LEFT JOIN sys.index_columns AS ic ON ic.object_id = c.object_id AND c.column_id = ic.column_id
    LEFT JOIN sys.indexes AS i ON i.object_id = ic.object_id AND i.index_id = ic.index_id
WHERE        
    o.type IN ('U','V')
    AND s.principal_id = 1
    AND t.name != 'sysname'
ORDER BY 
        s.name,
        o.name,
        c.column_id
	
--------------------------------------------
-- DataWarehouses with multiple Databases: 
--------------------------------------------


if OBJECT_ID('tempdb..#dictionary') IS NOT NULL
	drop table #dictionary
create table #dictionary ( [Database Name] varchar(90), [Schema Name] varchar(90), [Object Name] varchar(90), [Field Name] varchar(90), [Field Type] varchar(90), [Entity Type] varchar(10), [is_primary_key] int, [Field Length] int, [Precision] int, [rows] bigint )

declare @SQL nvarchar(max)

set @SQL = ''
--select * from sys.databases 
select @SQL = @SQL + CHAR(13) + 'USE ' + QUOTENAME([name]) + ';
 INSERT INTO #dictionary SELECT convert(varchar(140),' +quotename([name],'''') + ') as [Database Name], 
	convert(varchar(140),s.name) AS [Schema Name],
	convert(varchar(140),o.name) AS [Object Name],
	convert(varchar(140),c.name) AS [Field Name],    
	convert(varchar,t.name) AS [Field Type] ,
	convert( varchar, CASE 
		WHEN o.type = ''U'' THEN ''Table'' 
		WHEN o.type = ''V'' THEN ''View'' 
		ELSE o.type 
	END) AS [Entity Type],
	ISNULL(i.is_primary_key, 0) AS is_primary_key,
	c.max_length AS [Field Length],
	c.precision AS [Precision],
	p.rows
	FROM 
		sys.schemas s  
		INNER JOIN sys.objects AS o ON s.schema_id = o.schema_id
		INNER JOIN sys.columns c ON o.object_id = c.object_id
		INNER JOIN systypes t ON c.system_type_id = t.xtype    
				JOIN sys.partitions p on o.object_id=p.object_id
		LEFT JOIN  sys.index_columns AS ic ON ic.object_id = c.object_id AND c.column_id = ic.column_id  
		LEFT JOIN  sys.indexes AS i ON i.object_id = ic.object_id AND i.index_id = ic.index_id  
	where 
		o.type in (''U'',''V'')      
		and s.principal_id = 1
	order by 
		s.name,
		o.name,
		c.column_id' 
    FROM sys.databases 
    -- WHERE sys.databases.name NOT IN (...) -- exclude databases you don't hvve permissions to

execute (@SQL)

select * from #dictionary
-- see also:
-- https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/querying-the-sql-server-system-catalog-faq?view=sql-server-2017

IF OBJECT_ID('tempdb.dbo.#some_table', 'U') IS NOT NULL DROP TABLE #some_table;