If you ever need to find out whether a database has any tables that don’t have a primary key, you can run any of the following scripts in SQL Server to return just those tables.

All of these scripts take advantage of the OBJECTPROPERTY() function. This function accepts a TableHasPrimaryKey argument that you can check for a value of 0. If it’s 0, the table doesn’t have a primary key. If it’s 1 it does. Therefore, you can also use this function to return all tables with a primary key.

These scripts simply return the name of the table and its schema, but you can always modify them to return more columns.

Option 1 – OBJECTPROPERTY() with sys.tables

The sys.tables system view is probably the most obvious place to start. This view returns a row for each user table, and when we use OBJECTPROPERTY() to filter the results based on the TableHasPrimaryKey property being 0, we get just those tables without a primary key.

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

Result:

Changed database context to 'Test'.
+----------+--------------------+
| Schema   | Table              |
|----------+--------------------|
| dbo      | Datetime2Test      |
| dbo      | Datetime2Test2     |
| dbo      | DatetimeoffsetTest |
| dbo      | Individual         |
| dbo      | Occupation         |
| dbo      | Team               |
| dbo      | TimeTest           |
+----------+--------------------+
(7 rows affected)

In this case, my current database is a test database with a bunch of tables without primary keys.

If I run the same statement on another database, I get no results:

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

Result:

Changed database context to 'Music'.
(0 rows affected)

Option 2 – OBJECTPROPERTY() with INFORMATION_SCHEMA.TABLES

This example is similar to the previous one, except this time I’m querying the INFORMATION_SCHEMA.TABLES view. The information schema views included in SQL Server comply with the ISO standard definition for the INFORMATION_SCHEMA.

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

Result:

Changed database context to 'Test'.
+----------------+--------------------+
| TABLE_SCHEMA   | TABLE_NAME         |
|----------------+--------------------|
| dbo            | Datetime2Test      |
| dbo            | Datetime2Test2     |
| dbo            | DatetimeoffsetTest |
| dbo            | Individual         |
| dbo            | Occupation         |
| dbo            | Team               |
| dbo            | TimeTest           |
+----------------+--------------------+
(7 rows affected)

Option 3 – OBJECTPROPERTY() with sys.objects

In this example, I query the sys.objects view. This is a more general view when compared to the previous two, and it returns information about schema-scoped objects (not just tables). Because of this, we need to filter the results using type = 'U'. The U here stands for user-defined table.

Again, we can use the OBJECTPROPERTY() function to filter the results to just those tables that don’t have a primary key.

USE Test;
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') = 0
ORDER BY [Schema], [Table]

Result:

Changed database context to 'Test'.
+----------+--------------------+
| Schema   | Table              |
|----------+--------------------|
| dbo      | Datetime2Test      |
| dbo      | Datetime2Test2     |
| dbo      | DatetimeoffsetTest |
| dbo      | Individual         |
| dbo      | Occupation         |
| dbo      | Team               |
| dbo      | TimeTest           |
+----------+--------------------+
(7 rows affected)

We could alternatively filter it by type_desc = 'USER_TABLE', which would produce the same result.

USE Test;
SELECT 
  SCHEMA_NAME(schema_id) AS [Schema],
  name AS [Table]
FROM sys.objects 
WHERE type_desc = 'USER_TABLE'
AND OBJECTPROPERTY(OBJECT_ID(CONCAT(SCHEMA_NAME(schema_id), '.', name)), 'TableHasPrimaryKey') = 0
ORDER BY [Schema], [Table]

Result:

Changed database context to 'Test'.
+----------+--------------------+
| Schema   | Table              |
|----------+--------------------|
| dbo      | Datetime2Test      |
| dbo      | Datetime2Test2     |
| dbo      | DatetimeoffsetTest |
| dbo      | Individual         |
| dbo      | Occupation         |
| dbo      | Team               |
| dbo      | TimeTest           |
+----------+--------------------+
(7 rows affected)