T-SQL find all identity columns

Here’s a T-SQL find all identity columns in a 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

The sys.columns view lists column information for tables/views/table valued functions. If a column is an identity column then sys.columns.is_identity is 1 else it is 0. This is how we can list all identity columns for all tables in a database. The output from above query is shown below.

1_t sql find all identity columns

Another way to get this information is from sys.identity_columns view.  It contains details of all identity columns in a database along with their seed value, increment value and lots other information. The query for the same is given below.

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
GO

The output from the above query is shown below.

2_t sql find all identity columns

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 *