Cheat Code for Database Meta information : Sql Server

This post acts as a quick reference/Cheat code for anyone who would like to query the meta information about different entities in a database using Sql Server.

Get all Views in the Database

SELECT NAME,
       Object_definition (Object_id(NAME))
FROM   sys.views  

Get All Tables in the Database

SELECT NAME
FROM   sys.tables  

Get all columns from a table

SELECT c.NAME,
       c.max_length,
       c.is_nullable,
       c.is_identity,
       t.NAME AS DbType
FROM   sys.columns AS c
       INNER JOIN sys.types AS t
               ON c.user_type_id = t.user_type_id
       LEFT OUTER JOIN sys.default_constraints AS d
                    ON c.default_object_id = d.object_id
WHERE ( c.object_id = Object_id('<TableName>') )

Get All Indices from database

 SELECT t.NAME               AS ParentTable,
       ind.NAME,
       col.NAME             AS ColumnName,
       ind.type_desc        AS Type,
       ic.is_descending_key AS IsDescending,
       ind.is_unique,
       ind.index_id
FROM   sys.indexes ind
       INNER JOIN sys.index_columns ic
               ON ind.object_id = ic.object_id
                  AND ind.index_id = ic.index_id
       INNER JOIN sys.columns col
               ON ic.object_id = col.object_id
                  AND ic.column_id = col.column_id
       INNER JOIN sys.tables t
               ON ind.object_id = t.object_id
WHERE  ind.is_primary_key = 0
       AND ind.is_unique_constraint = 0
ORDER  BY ind.index_id  

Get All Primary Key Constraints

 SELECT TC.table_name,
       TC.constraint_name,
       CCU.column_name AS ColumnName
FROM   information_schema.table_constraints AS TC
       JOIN information_schema.constraint_column_usage AS CCU
         ON TC.constraint_name = CCU.constraint_name
WHERE  TC.table_name = '<TableName>'
       AND TC.constraint_type = 'PRIMARY KEY'  

Get all Unique Key Constraints

select TC.TABLE_NAME, 
                TC.CONSTRAINT_NAME,
                CCU.COLUMN_NAME as ColumnName
                from INFORMATION_SCHEMA.TABLE_CONSTRAINTS as TC   
                Join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE as CCU On TC.CONSTRAINT_NAME = CCU.CONSTRAINT_NAME
                where TC.TABLE_NAME = '<TableName>' and TC.CONSTRAINT_TYPE = 'UNIQUE'

Get all Check Constraints

select TC.TABLE_NAME, 
                   TC.CONSTRAINT_NAME ,
                   CCU.COLUMN_NAME as ColumnName ,
                   CC.CHECK_CLAUSE 
                   from INFORMATION_SCHEMA.TABLE_CONSTRAINTS as TC   
                    Join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE as CCU On  TC.CONSTRAINT_NAME = CCU.CONSTRAINT_NAME 
                    Join INFORMATION_SCHEMA.check_constraints as CC On CC.CONSTRAINT_NAME = TC.CONSTRAINT_NAME
                    where TC.TABLE_NAME = '<TableName>' and TC.CONSTRAINT_TYPE = 'CHECK'

Get all Foreign Key Constraints

 SELECT RC.constraint_name,
       KF.table_name  AS TableName,
       KF.column_name AS ColumnName,
       KP.table_name  AS ReferenceTable,
       KP.column_name AS ReferenceColumn
FROM   information_schema.referential_constraints RC
       JOIN information_schema.key_column_usage KF
         ON RC.constraint_name = KF.constraint_name
       JOIN information_schema.key_column_usage KP
         ON RC.unique_constraint_name = KP.constraint_name
WHERE  KF.table_name = '<TableName>'  

Get all Default value Constraints

 SELECT con.[name],
       col.[name] AS ColumnName,
       con.[definition],
       ty.NAME    AS DbType
FROM   sys.default_constraints con
       LEFT OUTER JOIN sys.objects t
                    ON con.parent_object_id = t.object_id
       LEFT OUTER JOIN sys.all_columns col
                    ON con.parent_column_id = col.column_id
                       AND con.parent_object_id = col.object_id
       INNER JOIN sys.types AS ty
               ON col.user_type_id = ty.user_type_id
WHERE  t.NAME = '<TableName>'
ORDER  BY con.NAME  

That’s it from Sql Server. We will attempt to retrieve the same set of information from other Db sources as well in future posts.

One thought on “Cheat Code for Database Meta information : Sql Server

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s