Find all Tables with and without Identity column in SQL Server Database

To find all user tables with an Identity column in SQL Server database, use this query:

SELECT DB_NAME() AS Database_Name
, sc.name AS Schema_Name
, t.name AS Table_Name
, ic.name AS Identity_Column_Name
FROM sys.tables t
INNER JOIN sys.schemas sc ON t.schema_id = sc.schema_id
INNER JOIN sys.identity_columns ic on t.object_id = ic.object_id
WHERE OBJECTPROPERTY(t.object_id,'TableHasIdentity') = 1
AND t.type = 'U'
ORDER BY t.name

It shows the table name and the name of the identity column. Only one identity column can be created per table.

To find all user tables without an Identity column in SQL Server database, use this query:

SELECT DB_NAME() AS Database_Name
, sc.name AS Schema_Name
, t.name AS Table_Name
FROM sys.tables t
INNER JOIN sys.schemas sc ON t.schema_id = sc.schema_id
WHERE OBJECTPROPERTY(t.object_id,'TableHasIdentity') = 0
AND t.type = 'U'
ORDER BY t.name