Getting the List of Primary Key and Foreign Key for a Particular Table in SQL Server

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

Getting Primary Key Columns

To get the list of primary key columns for a particular table, you can use the following query:

SELECT 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.TABLE_NAME = 'YourTableName' AND tc.CONSTRAINT_TYPE = 'PRIMARY KEY'; 

Replace `'YourTableName'` with the name of the table for which you want to get the primary key columns.

Getting Foreign Key Columns

To get the list of foreign key columns for a particular table, you can use the following query:

SELECT 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.TABLE_NAME = 'YourTableName' AND tc.CONSTRAINT_TYPE = 'FOREIGN KEY'; 

Replace `'YourTableName'` with the name of the table for which you want to get the foreign key columns.

Example

Let's say we have two tables `Orders` and `Customers` with the following structure:

CREATE TABLE Customers ( CustomerID INT PRIMARY KEY, Name NVARCHAR(50), Address NVARCHAR(100) ); CREATE TABLE Orders ( OrderID INT PRIMARY KEY, CustomerID INT, OrderDate DATE, FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID) ); 

To get the list of primary key and foreign key columns for the `Orders` table, we can use the following queries:

-- Get primary key columns 
SELECT 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.TABLE_NAME = 'Orders' AND tc.CONSTRAINT_TYPE = 'PRIMARY KEY'; -- Get foreign key columns 
SELECT 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.TABLE_NAME = 'Orders' AND tc.CONSTRAINT_TYPE = 'FOREIGN KEY'; 

The results will be:

Primary Key Columns

Foreign Key Columns