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 🙂

To get the next identity value from SQL Server

If you have table with no. of rows and you want to find out next id, following query can be used to identify:

SELECT IDENT_CURRENT(‘<Table Name>’) + IDENT_INCR(‘<Table Name>’)

You cannot reliably find out the next identity value – until you’ve actually inserted a new row into the table.

From the IDENT_CURRENT documentation:

When the IDENT_CURRENT value is NULL (because the table has never contained rows or has been truncated), the IDENT_CURRENT function returns the seed value.

Happy Coding 🙂