How do you list the primary key of a SQL Server table?

Question

Simple question, how do you list the primary key of a table with T-SQL? I know how to get indexes on a table but can't remember how to get the PK.

Answer 1

Try using the code given below:

SELECT Col.Column_Name from 

    INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tab, 

    INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE Col 

WHERE 

    Col.Constraint_Name = Tab.Constraint_Name

    AND Col.Table_Name = Tab.Table_Name

    AND Constraint_Type = 'PRIMARY KEY'

    AND Col.Table_Name = '<your table name>'

You can master these queries and become proficient in SQL queries by enrolling in an industry-recognized SQL certification.

Answer 2

So, for listing the primary key of a table with T-SQL, we can use the following query:

• SELECT Column_name FROM Information_schema.Table_Constraints abc

• JOIN Information_schema.key_column_usage xyz ON abc.constraint_Name = xyz.constraint_Name  

           WHERE abc.Table_Name = “name your table name here” AND 

           abc.Constraint_Type= “primary key”;

So, this will give you all the columns that are the primary key in your table.

Answer 3

In an attempt to find out which column(s) in a table on SQL Server can help distinguish each row uniquely, one can either use the INFORMATION_SCHEMA views or the sys catalog views. These special tables assist in determining the primary key columns. Such knowledge is critical in appreciating the structure of the data within the table and the best ways to access specific record types.

 

If you want to implement follow this:

SELECT 

    kcu.COLUMN_NAME

FROM 

    INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc

JOIN 

    INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu 

    ON tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME

WHERE 

    tc.TABLE_NAME = 'your_table_name' 

    AND tc.CONSTRAINT_TYPE = 'PRIMARY KEY';

SELECT 

    c.name AS Column_Name

FROM 

    sys.key_constraints kc

JOIN 

    sys.index_columns ic ON kc.object_id = ic.object_id AND kc.type = 'PK'

JOIN 

    sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id

WHERE 

    kc.name = 'PK_your_table_name';  -- Replace with your actual primary key name or omit for all PKs