How to Find All Identity Columns in the Database

In this article, we will learn How to Find all Identity Columns in the Database. You can typically query the system catalog or information schema, to find identity columns in different database management systems.

Here’s a Query to find all identity columns in a database.

Using SQL Server to Find All Identity Columns in the Database

SELECT 
	OBJECT_SCHEMA_NAME(tables.object_id, db_id())
	AS SchemaName,
	tables.name As TableName,
	columns.name as ColumnName
FROM sys.tables tables 
	JOIN sys.columns columns 
ON tables.object_id=columns.object_id
WHERE columns.is_identity=1

This query retrieves the table and column names of identity columns in a Database.

Another way to find all identity columns in the Database. It contains details of all identity columns in a database along with their seed value, increment value, and other information. The below query for the same is given.

SELECT 
	OBJECT_SCHEMA_NAME(tables.object_id, db_id())
	AS SchemaName,
	tables.name As TableName,
	identity_columns.name as ColumnName,
	identity_columns.seed_value,
	identity_columns.increment_value,
	identity_columns.last_value
FROM sys.tables tables 
	JOIN sys.identity_columns identity_columns 
ON tables.object_id=identity_columns.object_id