7 Ways to Return All Tables with a Primary Key in SQL Server

If you ever find yourself needing to find all tables that have a primary key in SQL Server, this article can help.

This article offers seven ways to return all tables in the current database that have a primary key.

Note that most of these examples return only the tables – not the primary keys themselves. If you want a list of primary keys, see 11 Ways to Return a Primary Key in SQL Server.

Option 1 – OBJECTPROPERTY() with sys.tables

The first option involves using the OBJECTPROPERTY() function when querying the sys.tables system view. This function accepts a TableHasPrimaryKey argument. If this argument has a value of 1, we get all tables that have a primary key (if it’s 0 then we get all tables that don’t have a primary key).

SELECT 
  SCHEMA_NAME(schema_id) AS [Schema], 
  name AS [Table]
FROM sys.tables
WHERE OBJECTPROPERTY(object_id, 'TableHasPrimaryKey') = 1
ORDER BY [Schema], [Table];

Result:

+----------+---------+
| Schema   | Table   |
|----------+---------|
| dbo      | Albums  |
| dbo      | Artists |
| dbo      | Country |
| dbo      | Genres  |
+----------+---------+

In this example, the current database has four tables with a primary key.

The remaining examples will query the same database, so the results will be the same for those queries.

Option 2 – OBJECTPROPERTY() with INFORMATION_SCHEMA.TABLES

This example uses OBJECTPROPERTY() again, but this time I’m querying the INFORMATION_SCHEMA.TABLES view.

SELECT 
  TABLE_SCHEMA,
  TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE OBJECTPROPERTY(OBJECT_ID(CONCAT(TABLE_SCHEMA, '.', TABLE_NAME)),'TableHasPrimaryKey') = 1 AND
TABLE_TYPE='BASE TABLE'
ORDER BY TABLE_SCHEMA, TABLE_NAME;

Result:

+----------------+--------------+
| TABLE_SCHEMA   | TABLE_NAME   |
|----------------+--------------|
| dbo            | Albums       |
| dbo            | Artists      |
| dbo            | Country      |
| dbo            | Genres       |
+----------------+--------------+

Option 3 – OBJECTPROPERTY() with sys.objects

Once again OBJECTPROPERTY() comes to the rescue. This time I’m querying the sys.objects system view.

SELECT 
  SCHEMA_NAME(schema_id) AS [Schema],
  name AS [Table]
FROM sys.objects 
WHERE type = 'U'
AND OBJECTPROPERTY(OBJECT_ID(CONCAT(SCHEMA_NAME(schema_id), '.', name)), 'TableHasPrimaryKey') = 1
ORDER BY [Schema], [Table]

Result:

+----------+---------+
| Schema   | Table   |
|----------+---------|
| dbo      | Albums  |
| dbo      | Artists |
| dbo      | Country |
| dbo      | Genres  |
+----------+---------+

Option 4 – INFORMATION_SCHEMA.TABLE_CONSTRAINTS

You can query the INFORMATION_SCHEMA.TABLE_CONSTRAINTS view to get a list of tables with primary keys. You need to filter the results to only those rows that have a CONSTRAINT_TYPE of PRIMARY KEY.

SELECT  
  CONSTRAINT_SCHEMA, 
  TABLE_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'PRIMARY KEY';

Result:

+---------------------+--------------+
| CONSTRAINT_SCHEMA   | TABLE_NAME   |
|---------------------+--------------|
| dbo                 | Artists      |
| dbo                 | Genres       |
| dbo                 | Albums       |
| dbo                 | Country      |
+---------------------+--------------+

This view also returns the constraint name, so you can also include that column if required:

SELECT  
  CONSTRAINT_SCHEMA, 
  TABLE_NAME,
  CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'PRIMARY KEY';

Result:

+---------------------+--------------+-------------------------------+
| CONSTRAINT_SCHEMA   | TABLE_NAME   | CONSTRAINT_NAME               |
|---------------------+--------------+-------------------------------|
| dbo                 | Artists      | PK__Artists__25706B50FCD918B1 |
| dbo                 | Genres       | PK__Genres__0385057E88BB96F8  |
| dbo                 | Albums       | PK__Albums__97B4BE379FC780BD  |
| dbo                 | Country      | PK__Country__10D1609F97ADEC31 |
+---------------------+--------------+-------------------------------+

Option 5 – sys.key_constraints

You can filter the sys.key_constraints view a CONSTRAINT_TYPE of PK to get a list of tables with primary keys.

SELECT 
  SCHEMA_NAME(schema_id) AS [Schema],
  OBJECT_NAME(parent_object_id) AS [Table]
FROM sys.key_constraints 
WHERE type = 'PK';

Result:

+----------+---------+
| Schema   | Table   |
|----------+---------|
| dbo      | Artists |
| dbo      | Genres  |
| dbo      | Albums  |
| dbo      | Country |
+----------+---------+

Here it is again with the primary key name:

SELECT 
  SCHEMA_NAME(schema_id) AS [Schema],
  OBJECT_NAME(parent_object_id) AS [Table],
  name
FROM sys.key_constraints 
WHERE type = 'PK';

Result:

+----------+---------+-------------------------------+
| Schema   | Table   | name                          |
|----------+---------+-------------------------------|
| dbo      | Artists | PK__Artists__25706B50FCD918B1 |
| dbo      | Genres  | PK__Genres__0385057E88BB96F8  |
| dbo      | Albums  | PK__Albums__97B4BE379FC780BD  |
| dbo      | Country | PK__Country__10D1609F97ADEC31 |
+----------+---------+-------------------------------+

Option 6 – sys.objects

The sys.objects system view is a popular one for returning information about schema-scoped objects, including primary keys.

SELECT 
  SCHEMA_NAME(schema_id) AS [Schema],
  OBJECT_NAME(parent_object_id) AS [Table]
FROM sys.objects 
WHERE type = 'PK';

Result:

+----------+---------+
| Schema   | Table   |
|----------+---------|
| dbo      | Artists |
| dbo      | Genres  |
| dbo      | Albums  |
| dbo      | Country |
+----------+---------+

As with the previous two examples, we can include the name column of this view to display the name of the primary key:

SELECT 
  SCHEMA_NAME(schema_id) AS [Schema],
  OBJECT_NAME(parent_object_id) AS [Table],
  name
FROM sys.objects 
WHERE type = 'PK';

Result:

+----------+---------+-------------------------------+
| Schema   | Table   | name                          |
|----------+---------+-------------------------------|
| dbo      | Artists | PK__Artists__25706B50FCD918B1 |
| dbo      | Genres  | PK__Genres__0385057E88BB96F8  |
| dbo      | Albums  | PK__Albums__97B4BE379FC780BD  |
| dbo      | Country | PK__Country__10D1609F97ADEC31 |
+----------+---------+-------------------------------+

Option 7 – OBJECTPROPERTYEX()

The OBJECTPROPERTYEX() function works just like the OBJECTPROPERTY() function, except that it supports more properties. Therefore, any of the previous examples that use OBJECTPROPERTY(), could easily be rewritten to use OBJECTPROPERTYEX().

For example, I could rewrite the first example on this page to the following:

SELECT 
  SCHEMA_NAME(schema_id) AS [Schema], 
  name AS [Table]
FROM sys.tables
WHERE OBJECTPROPERTYEX(object_id, 'TableHasPrimaryKey') = 1
ORDER BY [Schema], [Table];

Result:

+----------+---------+
| Schema   | Table   |
|----------+---------|
| dbo      | Albums  |
| dbo      | Artists |
| dbo      | Country |
| dbo      | Genres  |
+----------+---------+

I should mention that OBJECTPROPERTYEX() returns a sql_variant data type, whereas OBJECTPROPERTY() returns an int.