In this blog we will discuss how we can get primary column name of any table programmatically. However we can see primary column name by just right clicking on a table and see design view. But when we have dynamically create table and set primary key then might be we does not know the primary column name. So in this article we discuss about that.
 
In my database I have only one table named Employee, and you can see in the below image the primary key column is EmployeeId.
 
Get Primary Key Column In SQL Server
Here we use INFORMATION_SCHEMA.TABLE_CONSTRAINTS and INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE view so first we get what's inside that view.
 
Run the following queries to get the output of both views.
 
INFORMATION_SCHEMA.TABLE_CONSTRAINTS
    select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS   

Output

 
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
    select * from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE   

Output

 
Get Primary Key Column In SQL Server
 

Here are a few lines of sql query using which we can get the primary column name.
    select C.COLUMN_NAME FROM  
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS T  
    JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE C  
    ON C.CONSTRAINT_NAME=T.CONSTRAINT_NAME  
    WHERE  
    C.TABLE_NAME='Employee'  
    and T.CONSTRAINT_TYPE='PRIMARY KEY'   

Output

 
Explanation
    Here we join these two views, TABLE_CONSTRAINTS And CONSTRAINT_COLUMN_USAGE on CONSTRAINT_NAME.
    Then select those records where CONSTRAINT_COLUMN_USAGE.TABLE_NAME is Employee and TABLE_CONSTRAINTS.CONSTRAINT_TYPE is Primary Key.
    And then select CONSTRAINT_COLUMN_USAGE. COLUMN_NAME.

So that is just two or three lines of sql query for getting primary column name of any table. I hope you find some useful information in this article. If you find this helpful kindly share it with your friends.

HostForLIFEASP.NET SQL Server 2019 Hosting