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

Retrieving Table Primary Key in SQL Server Using SQL Query

In SQL Server, there are a couple of approaches to retrieve the primary key of a specific table. Let's explore them:

Approach 1:

This query selects the column name that is designated as the primary key using OBJECTPROPERTY function:

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'

Approach 2:

Alternatively, you can use a system function called sys.primary_keys to retrieve the primary key information:

SELECT
  name,
  primary_key_id,
  object_id
FROM
  sys.primary_keys
WHERE
  object_id = OBJECT_ID('TableName')
ORDER BY
  primary_key_id;

Comparison with MySQL Query:

Note that the provided MySQL query differs slightly from the SQL Server approaches. In SQL Server, you cannot use the SHOW KEYS statement to explicitly indicate a specific Key_name. Instead, you need to specify the CONSTRAINT_NAME of the primary key constraint using OBJECTPROPERTY.

Ideal Query for Both MySQL and SQL Server:

Unfortunately, there is no direct SQL query that works seamlessly for both MySQL and SQL Server to retrieve the primary key. However, you can employ a dynamic query that queries different system tables based on the database type, as shown below:

-- Check the database type
IF @@SERVERNAME LIKE '%MySQL%'
BEGIN
  SET @sqlQuery = 'SHOW KEYS FROM tablename WHERE Key_name = ''PRIMARY''';
END
ELSE
BEGIN
  SET @sqlQuery = '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''';
END

EXEC sp_executesql @sqlQuery;

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