Reading Time: <1 minutes
Hello people,
Good Morning!
In this quick post I will demonstrate how to identify tables that have IDENTITY columns in SQL Server. Often we need to identify which tables are these, what is the current value of identity and what is the value of the table seed and of course we will not look table by table.
For this, we can quickly identify this information with the query below:
| 1 2 3 4 5 6 7 8 9 10 11 12 | SELECT IDENT_SEED(TABLE_NAME) AS Seed, IDENT_INCR(TABLE_NAME) AS Increment, IDENT_CURRENT(TABLE_NAME) AS Current_Identity, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'TableHasIdentity') = 1 AND TABLE_TYPE = 'BASE TABLE' ORDER BY Current_Identity DESC |
Example:
Identifying the columns that have the Identity property:
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT, ORDINAL_POSITION, NUMERIC_PRECISION, NUMERIC_SCALE FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1 ORDER BY TABLE_NAME |
Example:
To the next!
Hey Dirceu, how are you?
First thanks for sharing your knowledge!
I would like help with a question, in addition to bringing all this information related to identity, would you also have the name of the column that would be identity?
Att
Philip Oliveira
Oops, I edited the post 🙂