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. 


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 =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. 
Previous
Next Post »

1 comments:

Click here for comments
March 18, 2016 ×

Came to your article for some other query, but now i am really curious to know how you added code conversion box?

Congrats bro Robert Brunelle you got PERTAMAX...! hehehehe...
Reply
avatar

If you have any question about any post, Feel free to ask.You can simply drop a comment below post or contact via Contact Us form. Your feedback and suggestions will be highly appreciated. Also try to leave comments from your account not from the anonymous account so that i can respond to you easily..