Getting All Table Names with No Primary Key in SQL Server

In SQL Server, you can retrieve a list of all table names that do not have a primary key using the `sys.tables` and `sys.indexes` system views. Here's how to do it.

Syntax

The syntax to get all table names with no primary key is as follows:

SELECT t.name FROM sys.tables t LEFT JOIN sys.indexes i ON t.object_id = i.object_id AND i.is_primary_key = 1 WHERE i.object_id IS NULL; 

This query returns a list of all table names that do not have a primary key.

Example

Here's an example of how to execute the query:

USE YourDatabaseName; GO SELECT t.name FROM sys.tables t LEFT JOIN sys.indexes i ON t.object_id = i.object_id AND i.is_primary_key = 1 WHERE i.object_id IS NULL; GO 

In this example, we switch to the database where we want to retrieve the table names, and then execute the query.

Example Output

The output of the query will be a list of all table names that do not have a primary key. Here's an example output:

In this example, the output includes table names like `Table1`, `Table2`, and `Table3` that do not have a primary key.

How it Works

The query uses a `LEFT JOIN` to join the `sys.tables` and `sys.indexes` system views. The `sys.tables` view contains a list of all tables in the database, while the `sys.indexes` view contains a list of all indexes, including primary keys. The `LEFT JOIN` is used to include all tables, even if they do not have a primary key. The `WHERE` clause then filters out tables that have a primary key, by checking if the `object_id` column is `NULL`.