Getting the List of Primary Key and Foreign Key for the Entire Database in SQL Server

In SQL Server, you can get the list of primary key and foreign key for the entire database using the `INFORMATION_SCHEMA` database. Here's how to do it.

Getting Primary Key Columns

To get the list of primary key columns for the entire database, you can use the following query:

SELECT tc.TABLE_NAME, k.COLUMN_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS k ON tc.CONSTRAINT_NAME = k.CONSTRAINT_NAME WHERE tc.CONSTRAINT_TYPE = 'PRIMARY KEY'; 

This query returns a list of tables with their corresponding primary key columns.

Getting Foreign Key Columns

To get the list of foreign key columns for the entire database, you can use the following query:

SELECT tc.TABLE_NAME, k.COLUMN_NAME, fk.TABLE_NAME AS ReferencedTableName, fk.COLUMN_NAME AS ReferencedColumnName FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS k ON tc.CONSTRAINT_NAME = k.CONSTRAINT_NAME INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS rc ON tc.CONSTRAINT_NAME = rc.CONSTRAINT_NAME INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS fk ON rc.UNIQUE_CONSTRAINT_NAME = fk.CONSTRAINT_NAME WHERE tc.CONSTRAINT_TYPE = 'FOREIGN KEY'; 

This query returns a list of tables with their corresponding foreign key columns, along with the referenced table and column names.

Example

Let's say we have a database with several tables, including `Orders`, `Customers`, `Products`, and `OrderDetails`. We can use the above queries to get the list of primary key and foreign key columns for the entire database.

-- Get primary key columns 
SELECT tc.TABLE_NAME, k.COLUMN_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS k ON tc.CONSTRAINT_NAME = k.CONSTRAINT_NAME WHERE tc.CONSTRAINT_TYPE = 'PRIMARY KEY'; -- Get foreign key columns 
SELECT tc.TABLE_NAME, k.COLUMN_NAME, fk.TABLE_NAME AS ReferencedTableName, fk.COLUMN_NAME AS ReferencedColumnName FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS k ON tc.CONSTRAINT_NAME = k.CONSTRAINT_NAME INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS rc ON tc.CONSTRAINT_NAME = rc.CONSTRAINT_NAME INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS fk ON rc.UNIQUE_CONSTRAINT_NAME = fk.CONSTRAINT_NAME WHERE tc.CONSTRAINT_TYPE = 'FOREIGN KEY'; 

The results will be:

Primary Key Columns

Foreign Key Columns