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.

1_Find tables with identity columns in SQL Server

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

2_Find tables with identity columns in SQL Server

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

 
Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Leave a Reply

Your email address will not be published. Required fields are marked *