Getting the List of Tables without an Identity Column in SQL Server

In SQL Server, you can get the list of tables without an identity column using the `INFORMATION_SCHEMA` database. Here's how to do it.

Using the `INFORMATION_SCHEMA.COLUMNS` System View

You can use the `INFORMATION_SCHEMA.COLUMNS` system view to get the list of tables without an identity column. Here's the query:

SELECT t.TABLE_NAME FROM INFORMATION_SCHEMA.TABLES AS t LEFT JOIN INFORMATION_SCHEMA.COLUMNS AS c ON t.TABLE_NAME = c.TABLE_NAME AND c.IDENTITY_COLUMN = 'yes' WHERE c.TABLE_NAME IS NULL; 

This query joins the `INFORMATION_SCHEMA.TABLES` system view with the `INFORMATION_SCHEMA.COLUMNS` system view on the `TABLE_NAME` column. The `LEFT JOIN` is used to include all tables, even if they don't have an identity column. The `WHERE` clause filters out the tables that have an identity column.

Using the `sys.tables` and `sys.columns` System Views

You can also use the `sys.tables` and `sys.columns` system views to get the list of tables without an identity column. Here's the query:

SELECT t.name AS TABLE_NAME FROM sys.tables AS t LEFT JOIN sys.columns AS c ON t.object_id = c.object_id AND c.is_identity = 1 WHERE c.object_id IS NULL; 

This query joins the `sys.tables` system view with the `sys.columns` system view on the `object_id` column. The `LEFT JOIN` is used to include all tables, even if they don't have an identity column. The `WHERE` clause filters out the tables that have an identity column.

Example

Let's say we have a database with several tables, including `Orders`, `Customers`, `Products`, and `OrderDetails`. We can use the above queries to get the list of tables without an identity column.

-- Using INFORMATION_SCHEMA.COLUMNS 
SELECT t.TABLE_NAME FROM INFORMATION_SCHEMA.TABLES AS t LEFT JOIN INFORMATION_SCHEMA.COLUMNS AS c ON t.TABLE_NAME = c.TABLE_NAME AND c.IDENTITY_COLUMN = 'yes' WHERE c.TABLE_NAME IS NULL; 
-- Using sys.tables and sys.columns 
SELECT t.name AS TABLE_NAME FROM sys.tables AS t LEFT JOIN sys.columns AS c ON t.object_id = c.object_id AND c.is_identity = 1 WHERE c.object_id IS NULL; 

The results will be:

Tables without an Identity Column

In this example, the `Customers` and `Products` tables do not have an identity column.