How to Find the Primary Key of a SQL Server Table?

Get primary key information from SQL Server table

Determining the primary key of a SQL Server table is a common task for database administrators and developers. This knowledge is critical to maintaining data integrity and optimizing database access.

List primary keys

To retrieve primary key information for a specific table, execute the following T-SQL query:

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

Description

The
• INFORMATION_SCHEMA.TABLE_CONSTRAINTS table contains information about the constraints defined on the table.
The
• INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE table provides information about which columns participate in each constraint.
• This query uses a join between the two tables to filter constraints of type "PRIMARY KEY" and limit the results to the specified table name.

For example, to list the primary keys of the Customers table, use the following query:

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

This query will return the column name used as the primary key of the Customers table. I've rewritten the JOIN in the original query to a clearer INNER JOIN syntax.

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