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.