How to find all the tables with Identity Column in SQL Server Database - SQL Server / T-SQL Tutorial Part 45

Scenario:

How would you find all the tables in a SQL Server Database which has identity Column?

Solution:

We can use system tables such as sys.columns and sys.tables to get this information.

--Find out all the columns for all the tables on which Identity Property is enabled
SELECT DB_Name() AS DatabaseName
    ,OBJECT_NAME(c.OBJECT_ID) AS TableName
    ,c.NAME AS ColumnName
FROM YourDBName.sys.columns c
INNER JOIN sys.tables t ON c.object_id = t.object_id
WHERE is_identity = 1

How to get Tables in a Database with Identity Column in SQL Server - SQL Server/T-SQL Tutorial

Video Demo: How to find all tables which has identity Column in SQL Server