SQL Metadata load all databases all column data into a table

We had the idea to put all column data into a table for code review comparisons etc. Chris Trump came up with this. Thanks, Chris!

EXECUTE master.sys.sp_MSforeachdb @command1 = N'
USE [?];
 
INSERT INTO dbo.Database_DataDictionary (DATABASE_NAME, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE, DATETIME_PRECISION)
SELECT  
        "?" as DATABASE_NAME,
        t.TABLE_NAME,
        c.COLUMN_NAME,
        c.ORDINAL_POSITION,
        UPPER(c.DATA_TYPE) as DATA_TYPE,
        ISNULL(c.CHARACTER_MAXIMUM_LENGTH,'''') AS CHARACTER_MAXIMUM_LENGTH,
        ISNULL(c.NUMERIC_PRECISION,'''') AS NUMERIC_PRECISION,
        ISNULL(c.NUMERIC_SCALE,'''') AS NUMERIC_SCALE,
        ISNULL(c.DATETIME_PRECISION,'''') AS DATETIME_PRECISION
 
FROM         
        INFORMATION_SCHEMA.COLUMNS AS c 
        INNER JOIN INFORMATION_SCHEMA.TABLES AS t ON 
                t.TABLE_NAME = c.TABLE_NAME
WHERE         
        t.TABLE_TYPE = ''Base Table'''
 

SELECT * FROM dbo.Database_DataDictionary

Comments are closed.

Post Navigation