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.

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.

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