SQL Server Queries - Get list of tables without having identity column

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.