SQL Server: Get Primary Key Constraint And Foreign Key Constraint

If you want to know all primary key and foreign key constraints in your SQL Server data base or a particular data table then use the below SQL Statement.

Know Primary Key Constraint in Entire SQL Data Base:

SELECT I.name AS PrimaryKeyName,
OBJECT_NAME(IC.OBJECT_ID)AS TableName,
COL_NAME(IC.OBJECT_ID,IC.column_id)AS PrimaryKeyColumnName
FROMSYS.INDEXESAS I
INNERJOINSYS.INDEX_COLUMNSAS IC
ON I.OBJECT_ID= IC.OBJECT_ID
AND I.index_id = IC.index_id
WHERE I.is_primary_key = 1 ORDERBYOBJECT_NAME(IC.OBJECT_ID)

 

If you want to know Primary key constraint on any particular table then use the below statement:



To know Foreign Key Constraint use below SQL Statement:

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
FROMSYS.FOREIGN_KEYSAS FR
INNERJOINSYS.FOREIGN_KEY_COLUMNSAS FC
ON FR.OBJECT_ID= FC.constraint_object_id ORDERBYOBJECT_NAME(FR.parent_object_id)