This article provides the script to find all the Tables with or without an Identity column

Tables with Identity column

We can write a query like below to get all the Tables with Identity column:

SELECT name 'Table with Identity column'
FROM SYS.Tables
WHERE OBJECTPROPERTY(object_id,'TableHasIdentity') = 1
      AND type = 'U'

Tables without Identity column

We can write a query like below to get all the Tables without Identity column:

SELECT name 'Table without Identity column'
FROM SYS.Tables
WHERE OBJECTPROPERTY(object_id,'TableHasIdentity') = 0
      AND type = 'U'

EXAMPLE

Let us understand the above scripts with an example. To understand this, let us create a demo database SqlHintsDemoDB with Customers table having an identity column and an Orders table without identity column.

CREATE DATABASE SqlHintsDemoDB
GO
USE SqlHintsDemoDB
GO
/*Let us create Customers table with Identity column.*/
CREATE TABLE dbo.Customers (
    CustomerId int IDENTITY (1, 1) 
              PRIMARY KEY CLUSTERED NOT NULL ,
    FirstName Varchar(50),
    LastName Varchar(50))
GO
/*Let us create Orders Table without and Identity column.*/
CREATE TABLE dbo.Orders (
    OrderId int NOT NULL ,
    CustomerId int NOT NULL ,
    CreationDT DATETIME NOT NULL)
GO

Now let us run the queries to get the list of all Tables with or without identity column and verify the result: