How to Find a Table's Primary Key in SQL Server Using SQL Queries?

Finding Table Primary Key Using SQL Queries in SQL Server

In SQL Server, obtaining a table's primary key using a SQL query involves a slightly different approach compared to MySQL. Explore the following query options to retrieve primary key information:

SQL Server Query:

One commonly used query for SQL Server is:

SELECT name AS PrimaryKey
FROM sysobjects
WHERE xtype = 'PK' AND name IN (
    SELECT name 
    FROM sysindexes 
    WHERE id = object_id(TableName)
)

Replace TableName with the actual table name in the query.

Another SQL Server Query:

An alternative query for SQL Server is provided below:

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'

Again, substitute TableName and Schema with appropriate values.

Universal Query for MySQL and SQL Server (Not Recommended):

While there is no direct equivalent of the MySQL query mentioned in the question for SQL Server, one can employ a more complex query that should work in both database systems:

SELECT T.COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS AS T
LEFT JOIN 
    (SELECT DISTINCT C.TABLE_NAME, C.COLUMN_NAME
    FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS U
    JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS C
        ON U.TABLE_SCHEMA = C.TABLE_SCHEMA
        AND U.TABLE_NAME = C.TABLE_NAME
        AND U.CONSTRAINT_NAME = C.CONSTRAINT_NAME) AS S
    ON T.TABLE_NAME = S.TABLE_NAME AND T.COLUMN_NAME = S.COLUMN_NAME
WHERE T.TABLE_SCHEMA = 'TableName_Schema' AND T.TABLE_NAME = 'TableName'

While this query may function in both MySQL and SQL Server, it is less efficient and not recommended for optimal performance.

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