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.