A SQL Server IDENTITY column is a special type of column that is used to automatically generate key values based on a provided seed (starting point) and increment. SQL Server provides us with a number of functions that work with the IDENTITY column.
For example, suppose we have an Employee table and we want to generate EmployeeID automatically. We have a starting employee ID 100 and further want to increment each new EmpID by one. In this case, we need to define the following values 100 as Seed and 1 as Step.
We have a few useful Identity functions in SQL Server to work with the IDENTITY columns in a table. Let’s explore the following IDENTITY functions.
- SQL @@IDENTITY Function
- SQL SCOPE_IDENTITY() Function
- SQL IDENT_CURRENT Function
- SQL IDENTITY Function
But we want to show all the tables which have an IDENTITY column. We can use the query which returns the Seed Values, Increment Values, and Current Identity Column value of all the tables of a database.
|
1 2 3 4 5 6 7 8 9 |
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' |
You may like PIVOT and UNPIVOT in SQL Server.
SQL Server IDENTITY related values
The article was published on January 8, 2019 @ 1:45 PM
Leave a Comment