Get primary key information from SQL Server table

Accessing primary key information is critical for data integrity and efficient query execution when processing SQL Server tables. This article discusses how to use Transact-SQL (T-SQL) to list primary keys for SQL Server tables.

Query listing primary key

To get the primary key information of a specific table, execute the following query:

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 Tab.Constraint_Type = 'PRIMARY KEY'
  AND Col.Table_Name = ''

Query instructions:

• INFORMATION_SCHEMA.TABLE_CONSTRAINTS Provides information about constraints on tables, including primary keys.
• INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE Link the constraint to its applicable column.
• Tab.Constraint_Type = 'PRIMARY KEY' Filter the result to include only primary key constraints.
• Replace with the table name of which you want to list the primary key.

After this query is executed, you will retrieve a list of column names specified as part of the primary key in the specified table.