How to Retrieve a SQL Server Table's Primary Key?

Discovering SQL Server Primary Keys

Knowing a table's primary key in SQL Server is vital for data integrity and inter-table relationships. This guide demonstrates a simple T-SQL method for identifying this crucial information.

Identifying Primary Key Columns

Use this query to retrieve the primary key column(s) of a specific table:

<code class="language-sql">SELECT Col.Column_Name
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tab
JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE Col
ON Col.Constraint_Name = Tab.Constraint_Name
WHERE Col.Constraint_Type = 'PRIMARY KEY'
AND Col.Table_Name = '<Your Table Name>';</code>

Here's a breakdown:

• INFORMATION_SCHEMA.TABLE_CONSTRAINTS: This system table stores details on table constraints, including primary keys.
• INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE: This table links constraints to their respective columns.
• Col.Column_Name: This column returns the name(s) of the primary key column(s).
• <Your Table Name>: Replace this placeholder with your target table's name.

Practical Example

To find the primary key of the Orders table, use the following query:

<code class="language-sql">SELECT Col.Column_Name
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tab
JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE Col
ON Col.Constraint_Name = Tab.Constraint_Name
WHERE Col.Constraint_Type = 'PRIMARY KEY'
AND Col.Table_Name = 'Orders';</code>

The query's output will list the name(s) of the primary key column(s), for example, OrderID or OrderNumber.

The above is the detailed content of How to Retrieve a SQL Server Table's Primary Key?. For more information, please follow other related articles on the PHP Chinese website!