Wednesday, September 25

To display CONSTRAINT TYPE (Primary Key / Foreign Key) in a table, along with Table name, Column name and Constraint name

This Query can be used to find CONSTRAINT TYPE (Primary / Foreign) in a table, along with Table name, Column name and Constraint name.

SELECT 
   A.TABLE_NAME,
   A.CONSTRAINT_NAME,
   B.COLUMN_NAME
FROM 
   INFORMATION_SCHEMA.TABLE_CONSTRAINTS A,
   INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE B
WHERE 
    CONSTRAINT_TYPE = 'PRIMARY KEY
   AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
ORDER BY 
   A.TABLE_NAME


For example i will use query on  AdventureWorksDW2008R2

Result:

 CONSTRAINT_TYPE = 'PRIMARY KEY'   can be replaced with  CONSTRAINT_TYPE = 'FOREIGN KEY'  to display Foreign Key results.