You can use the OBJECTPROPERTY() function in SQL Server to check whether or not an object is a primary key.

To find out whether an object is a primary key, pass the object ID as the first argument, and IsPrimaryKey as the second argument. The function returns a 1 or a 0 depending on whether or not it’s a primary key.

A return value of 1 means that it is a primary key, and a value of 0 means that it’s not.

Example 1 – Basic Usage

Here’s a quick example to demonstrate.

USE PK_Test;
SELECT OBJECTPROPERTY(901578250, 'IsPrimaryKey') AS [IsPrimaryKey];

Result:

+----------------+
| IsPrimaryKey   |
|----------------|
| 1              |
+----------------+

In this case, the PK_Test database has an object with the ID provided, and it’s a primary key.

Example 2 – Getting the Object ID

If you know the object’s name, but not its ID, you can use the OBJECT_ID() function to retrieve the ID based on its name.

Example:

SELECT OBJECTPROPERTY(OBJECT_ID('PK_Musician'), 'IsPrimaryKey') AS [IsPrimaryKey];

Result:

+----------------+
| IsPrimaryKey   |
|----------------|
| 1              |
+----------------+

In this case I checked the same object from the previous example.

Here it is again with the ID output separately.

SELECT 
  OBJECT_ID('PK_Musician') AS [Object ID],
  OBJECTPROPERTY(OBJECT_ID('PK_Musician'), 'IsPrimaryKey') AS [IsPrimaryKey];

Result:

+-------------+----------------+
| Object ID   | IsPrimaryKey   |
|-------------+----------------|
| 901578250   | 1              |
+-------------+----------------+

Example 3 – When the Object is NOT a Primary Key

Here’s what happens when the object isn’t a primary key.

SELECT OBJECTPROPERTY(885578193, 'IsPrimaryKey') AS [IsPrimaryKey];

Result:

+----------------+
| IsPrimaryKey   |
|----------------|
| 0              |
+----------------+

In this case, the database does in fact have an object with that ID, but the object is actually a table, so I get a negative result.

Here it is again using OBJECT_ID().

SELECT 
  OBJECT_ID('Musician') AS [Object ID],
  OBJECTPROPERTY(OBJECT_ID('Musician'), 'IsPrimaryKey') AS [IsPrimaryKey],
  OBJECTPROPERTY(OBJECT_ID('Musician'), 'IsTable') AS [IsTable];

Result:

+-------------+----------------+-----------+
| Object ID   | IsPrimaryKey   | IsTable   |
|-------------+----------------+-----------|
| 885578193   | 0              | 1         |
+-------------+----------------+-----------+

I also checked to see if the object is a table, and the result is positive.

Example 4 – Object Doesn’t Exist

SQL Server assumes that the object ID is in the current database context. If you pass in an object ID from a different database, you’ll either get a NULL result or you’ll get incorrect results.

SELECT 
  OBJECTPROPERTY(OBJECT_ID('InvalidObject'), 'IsPrimaryKey') AS [InvalidObject],
  OBJECTPROPERTY(12345678, 'IsPrimaryKey') AS [12345678];

Result:

+-----------------+------------+
| InvalidObject   | 12345678   |
|-----------------+------------|
| NULL            | NULL       |
+-----------------+------------+

In this case the database contains no objects of that name or ID, and so I get a NULL result.

You’ll also get NULL on error or if you don’t have permission to view the object.