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:

code

  1. SELECT I.name AS PrimaryKeyName,  
  2. OBJECT_NAME(IC.OBJECT_ID)AS TableName,  
  3. COL_NAME(IC.OBJECT_ID,IC.column_id)AS PrimaryKeyColumnName  
  4. FROMSYS.INDEXESAS I  
  5. INNERJOINSYS.INDEX_COLUMNSAS IC  
  6. ON I.OBJECT_ID= IC.OBJECT_ID  
  7. AND I.index_id = IC.index_id  
  8. 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:

code

To know Foreign Key Constraint use below SQL Statement:
  1. SELECT FR.name AS ForeignKeyName,  
  2. OBJECT_NAME(FR.parent_object_id)AS TableName,  
  3. COL_NAME(FC.parent_object_id,  
  4. FC.parent_column_id)AS ColumnName,  
  5. OBJECT_NAME(FR.referenced_object_id)AS ReferenceTableName,  
  6. COL_NAME(fc.referenced_object_id,  
  7. fc.referenced_column_id)AS ReferenceColumnName  
  8. FROMSYS.FOREIGN_KEYSAS FR  
  9. INNERJOINSYS.FOREIGN_KEY_COLUMNSAS FC  
  10. ON FR.OBJECT_ID= FC.constraint_object_id ORDERBYOBJECT_NAME(FR.parent_object_id)