SQL SERVER: Find all Primary and Foreign Key Constraints on each or any table in database

Introduction: In this article I am going to share how to check what constraints(Primary & Foreign key) are implemented on each tables and on what columns in database. 

In previous related articles i explained How to check primary, foreign, unique key constraints in table and  Create table with auto increment primary key column and Difference between primary key and foreign key in sql and Create, check and drop foreign key constraint on table and Difference between temporary table and table variable in sql server

Implementation: Let’s write sample query to get all the primary & foreign Key constraints.

Query to check primary key constraints on all tables

SELECT I.name AS PrimaryKeyName,

OBJECT_NAME(IC.OBJECT_ID) AS TableName,

COL_NAME(IC.OBJECT_ID,IC.column_id) AS PrimaryKeyColumnName

FROM SYS.INDEXES AS I

INNER JOIN SYS.INDEX_COLUMNS AS IC

ON I.OBJECT_ID = IC.OBJECT_ID

AND I.index_id = IC.index_id

WHERE I.is_primary_key = 1 ORDER BY OBJECT_NAME(IC.OBJECT_ID)

Query to check primary key constraints in specific table

SELECT I.name AS PrimaryKeyName,

OBJECT_NAME(IC.OBJECT_ID) AS TableName,

COL_NAME(IC.OBJECT_ID,IC.column_id) AS PrimaryKeyColumnName

FROM SYS.INDEXES AS I

INNER JOIN SYS.INDEX_COLUMNS AS IC

ON I.OBJECT_ID = IC.OBJECT_ID

AND I.index_id = IC.index_id

WHERE I.is_primary_key = 1 AND OBJECT_NAME(IC.OBJECT_ID)='YourTableName'

ORDER BY OBJECT_NAME(IC.OBJECT_ID)

Query to check foreign key constraints in all tables

SELECT FR.name AS ForeignKeyName,

OBJECT_NAME(FR.parent_object_id) AS TableName,

COL_NAME(FC.parent_object_id,

FC.parent_column_id) AS ColumnName,

OBJECT_NAME (FR.referenced_object_id) AS ReferenceTableName,

COL_NAME(fc.referenced_object_id,

fc.referenced_column_id) AS ReferenceColumnName

FROM SYS.FOREIGN_KEYS AS FR

INNER JOIN SYS.FOREIGN_KEY_COLUMNS AS FC

ON FR.OBJECT_ID = FC.constraint_object_id ORDER BY OBJECT_NAME(FR.parent_object_id)

Query to check foreign key constraints in specific table

SELECT FR.name AS ForeignKeyName,

OBJECT_NAME(FR.parent_object_id) AS TableName,

COL_NAME(FC.parent_object_id,

FC.parent_column_id) AS ColumnName,

OBJECT_NAME (FR.referenced_object_id) AS ReferenceTableName,

COL_NAME(fc.referenced_object_id,

fc.referenced_column_id) AS ReferenceColumnName

FROM SYS.FOREIGN_KEYS AS FR

INNER JOIN SYS.FOREIGN_KEY_COLUMNS AS FC

ON FR.OBJECT_ID = FC.constraint_object_id 

AND OBJECT_NAME(FR.parent_object_id)='YourTableName' 

ORDER BY OBJECT_NAME(FR.parent_object_id)

Now over to you:

A blog is nothing without reader's feedback and comments. So please provide your valuable feedback so that i can make this blog better and If you like my work; you can appreciate by leaving your comments, hitting Facebook like button, following on Google+, Twitter, Linkedin and Pinterest, stumbling my posts on stumble upon and subscribing for receiving free updates directly to your inbox . Stay tuned and stay connected for more technical updates. 

• Tweet
• Share
• Share
• Share

Related Post