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

SQL Server: Retrieving Table Primary Key using SQL Query

In SQL Server databases, one can retrieve the primary key of a table using a specific SQL query. This query differs from the one used in MySQL databases.

SQL Server Query:

SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + '.' + QUOTENAME(CONSTRAINT_NAME)), 'IsPrimaryKey') = 1
AND TABLE_NAME = 'TableName' AND TABLE_SCHEMA = 'Schema'

Parameters:

• TableName: The name of the table whose primary key you want to retrieve.
• Schema: The schema to which the table belongs.

Example:

To retrieve the primary key of the "Products" table in the "dbo" schema:

SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE OBJECTPROPERTY(OBJECT_ID('dbo.Products'), 'IsPrimaryKey') = 1
AND TABLE_NAME = 'Products' AND TABLE_SCHEMA = 'dbo'

Alternative Query for Both MySQL and SQL Server:

While the above query is specific to SQL Server, there is an alternative query that can work for both MySQL and SQL Server:

SHOW COLUMNS FROM `TableName` WHERE `Key` = 'PRI'

This query will retrieve the column with the 'PRI' key, which represents the primary key in both MySQL and SQL Server.

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