CODEWITHSUNDEEP
sql-server
Shibu
Shibu

Reputation: 1542

How to fetch the complete metadata of a SQL Server database

I need to get the complete metadata for each tables within a database in SQL Server.

When I am using 

SELECT * 
FROM INFORMATION_SCHEMA.COLUMNS

I am not getting the PK and FK information. How can I get the PK and FK information along with other metadata columns?

Upvotes: 2

Views: 2741

Answers (1)

Shibu
Shibu

Reputation: 1542

SELECT c.* --c.TABLE_NAME, c.COLUMN_NAME,c.DATA_TYPE,  c.is_nullable
             ,CASE WHEN pk.COLUMN_NAME IS NOT NULL THEN 'PRIMARY KEY' ELSE '' END AS KeyType
             ,CASE WHEN fk.COLUMN_NAME IS NOT NULL THEN 'FOREIGN KEY' ELSE '' END AS KeyType,x1.ReferenceTableName
             ,x1.ReferenceColumnName
FROM INFORMATION_SCHEMA.COLUMNS c
LEFT JOIN (
            SELECT ku.TABLE_CATALOG,ku.TABLE_SCHEMA,ku.TABLE_NAME,ku.COLUMN_NAME
            FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc
            INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS ku
                ON tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
                AND tc.CONSTRAINT_NAME = ku.CONSTRAINT_NAME
         )   pk
        ON  c.TABLE_CATALOG = pk.TABLE_CATALOG
            AND c.TABLE_SCHEMA = pk.TABLE_SCHEMA
            AND c.TABLE_NAME = pk.TABLE_NAME
            AND c.COLUMN_NAME = pk.COLUMN_NAME 
LEFT JOIN (
            SELECT ku.TABLE_CATALOG,ku.TABLE_SCHEMA,ku.TABLE_NAME,ku.COLUMN_NAME
            FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc
            INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS ku
                ON tc.CONSTRAINT_TYPE = 'FOREIGN KEY'
                AND tc.CONSTRAINT_NAME = ku.CONSTRAINT_NAME
         )   fk
        ON  c.TABLE_CATALOG = fk.TABLE_CATALOG
            AND c.TABLE_SCHEMA = fk.TABLE_SCHEMA
            AND c.TABLE_NAME = fk.TABLE_NAME
            AND c.COLUMN_NAME = fk.COLUMN_NAME
LEFT JOIN(
SELECT f.name AS ForeignKey,
SCHEMA_NAME(f.SCHEMA_ID) SchemaName,
OBJECT_NAME(f.parent_object_id) AS TableName,
COL_NAME(fc.parent_object_id,fc.parent_column_id) AS ColumnName,
SCHEMA_NAME(o.SCHEMA_ID) ReferenceSchemaName,
OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
COL_NAME(fc.referenced_object_id,fc.referenced_column_id) AS ReferenceColumnName
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id
INNER JOIN sys.objects AS o ON o.OBJECT_ID = fc.referenced_object_id) x1
ON  c.TABLE_NAME = x1.TableName
AND c.COLUMN_NAME = x1.ColumnName
ORDER BY c.TABLE_NAME, c.ORDINAL_POSITION

Upvotes: 3

Related Questions