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

Rename SQL Table

You can rename a table in SQL Server in following way:

sp_rename OLD_TABLE_NAME, NEW_TABLE_NAME

You must require alteration permission.

Renaming table will not rename stored procedures user-defined functions or programs refer to that table. You must manually modify all object which refer renamed table.

Note: Think twice before you rename a table. If existing stored procedures user-defined functions or programs refer to that table, the name modification will give you an error.