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)