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