Find tables with identity columns in SQL Server

In this blog we’ll see T-SQL query to find tables with identity columns In SQL Server.  It can be done in two ways either by using Information_schema catalog or the system catalog views.

A T-SQL to find table with identity columns using Information_Schema is given below.

SELECT 
tables.TABLE_NAME,
IDENT_SEED(tables.TABLE_SCHEMA + '.' + tables.TABLE_NAME) 
AS Seed,
IDENT_INCR(tables.TABLE_SCHEMA + '.' + tables.TABLE_NAME) 
AS Increment,
IDENT_CURRENT(tables.TABLE_SCHEMA + '.' + tables.TABLE_NAME) 
AS Current_Identity
FROM INFORMATION_SCHEMA.TABLES tables 
WHERE OBJECTPROPERTY(OBJECT_ID(tables.TABLE_SCHEMA + '.' + tables.TABLE_NAME), 
	'TableHasIdentity') = 1
AND tables.TABLE_TYPE = 'BASE TABLE'
GO

The output from above query is given below.

A T-SQL to find all tables with identity columns using sys catalog views is given below.

SELECT 
	OBJECT_SCHEMA_NAME(tables.object_id, db_id())
	AS SchemaName,
	tables.name As TableName,
	columns.name as ColumnName,
	IDENT_SEED(OBJECT_SCHEMA_NAME(tables.object_id, db_id()) + '.' + tables.name) 
	AS Seed,
	IDENT_INCR(OBJECT_SCHEMA_NAME(tables.object_id, db_id()) + '.' + tables.name) 
	AS Increment,
	IDENT_CURRENT(OBJECT_SCHEMA_NAME(tables.object_id, db_id()) + '.' + tables.name) 
	AS Current_Identity
FROM sys.tables tables 
	JOIN sys.columns columns 
ON tables.object_id=columns.object_id
WHERE columns.is_identity=1

A key point to note is to include Schema name along with table name when passing values to function IDENT_SEED, IDENT_INCR and IDENT_CURRENT otherwise you the functions may return null values even if a table has an identity column.

Like us on FaceBook  |  Join the fastest growing SQL Server group on FaceBook