Check foreign key constraints in SQL Server Database

While searching for how to find Primary Key and Foreign Key relationship using INFORMATION_SCHEMA in SQL Server Database, I came across following query which works for me.

SELECT RC.CONSTRAINT_NAME AS ‘FK_Name’
,KF.TABLE_SCHEMA AS ‘FK_Schema’
,KF.TABLE_NAME AS ‘FK_Table’
,KF.COLUMN_NAME AS ‘FK_Column’
,RC.UNIQUE_CONSTRAINT_NAME AS ‘PK_Name’
,KP.TABLE_SCHEMA AS ‘PK_Schema’
,KP.TABLE_NAME AS ‘PK_Table’
,KP.COLUMN_NAME AS ‘PK_Column’
,RC.MATCH_OPTION AS ‘MatchOption’
,RC.UPDATE_RULE AS ‘UpdateRule’
,RC.DELETE_RULE AS ‘DeleteRule’
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.COLUMN_NAME LIKE ‘%<Column Name>%’


You can also find Foreign Key using EXEC sp_fkeys ‘<TABLE NAME>’


Happy Coding 🙂

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s