RSS Feed

Find Primary Key & Foreign Key in Table


Find Primary Key & Foreign Key in Table

This tutorial will help you to find the details of Primary Key & Foreign key in a given table. I have segregated this into two different queries i.e. one for finding Primary Key and other for finding Foreign key, to make it more clear.

In this tutorial, I am assuming the reader is aware of Inner Joins.
Note: I am here taking "Orders" as the given input table. You can change the table name as per your requirement.

Get Primary Key details using the below query:

USE Northwind
SELECT T.Table_Name ,TC.Constraint_Name, KCU.Column_Name AS 'Primary_Key_Column_name'
FROM INFORMATION_SCHEMA.TABLES T 
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC 
ON T.table_name=TC.table_name
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU
ON KCU.Constraint_Name =  TC.Constraint_Name
WHERE OBJECTPROPERTY(OBJECT_ID(T.TABLE_NAME),'TableHasPrimaryKey')=1 
       AND T.TABLE_TYPE='BASE TABLE' 
       AND TC.CONSTRAINT_TYPE='primary key' 
       AND T.Table_Name='Orders'

Get Foreign Key details using the below query:

SELECT T.Table_Name ,TC.Constraint_Name, KCU.Column_Name AS 'Foreign_Key_Column_name'
FROM INFORMATION_SCHEMA.TABLES T 
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC 
ON T.table_name=TC.table_name
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU
ON KCU.Constraint_Name =  TC.Constraint_Name
WHERE OBJECTPROPERTY(OBJECT_ID(T.TABLE_NAME),'TableHasForeignKey')=1 
       AND T.TABLE_TYPE='BASE TABLE' 
       AND TC.CONSTRAINT_TYPE='foreign key' 
       AND T.Table_Name='Orders'

In the above two queries, I am using inner joins between INFORMATION_SCHEMA.TABLES, INFORMATION_SCHEMA.TABLE_CONSTRAINTS and INFORMATION_SCHEMA.KEY_COLUMN_USAGE tables. The differences between these two queries are:

a) The condition to check whether it is foreign or primary constraint type i.e. AND TC.CONSTRAINT_TYPE='foreign key' or AND TC.CONSTRAINT_TYPE='primary key' respectively to find result accordingly.

b) The property passed as second parameter to OBJECTPROPERTY() method (i.e. 'TableHasForeignKey' or 'TableHasPrimaryKey').

Both queries will result into giving the table name, constraint name and the associated column name as output. In the query, I am joining the records with their Table_Name and Constraint_Name columns. I also filtered the joined query by specifying the condition: 
WHERE OBJECTPROPERTY(OBJECT_ID(T.TABLE_NAME),'TableHasPrimaryKey')=1 
       AND T.TABLE_TYPE='BASE TABLE' 
       AND TC.CONSTRAINT_TYPE='foreign key' 
       AND T.Table_Name='Orders'

In this condition, we are giving a command to fetch or join only those records which says record’s table must have property named "TableHasPrimaryKey" (to find primary key detail) or "TableHasForeignKey" (to find foreign key detail) (i.e. OBJECTPROPERTY( OBJECT_ID(T.TABLE_NAME),'TableHasPrimaryKey')=1) and type should be Base Table (INFORMATION_SCHEMA.TABLES.TABLE_TYPE='BASE TABLE') and contraint type is foreign key ( AND INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE='foreign key') or primary key (AND TC.CONSTRAINT_TYPE='primary key') and table name (orders) is given (AND INFORMATION_SCHEMA.TABLES.Table_Name='Orders').

I would like to add one more thing to end up this tutorial. There are two inbuilt stored procedures which can be used in place of above queries. These two stored procedures will find the details of Primary and foreign key for a given table.

Stored Procedures are:

sp_pkeys(for finding primary key details)
sp_fkeys(for finding foreign key details)

Example:

EXEC sp_pkeys 'Table Name'
EXEC sp_fkeys 'Table Name'