By: Greg Robidoux | Comments (1) | Related: > Identities
Problem
One feature of SQL Server that is used in probably just about every database is identity columns. Identity columns allow you to create an auto generated number to keep your data unique. In some cases there are not good candidates for primary keys, so identity columns are used instead and in a lot of cases these auto generated numbers have become the primary keys for a fair amount of tables in SQL Server databases. In a previous tip Managing and maintaining identity values we talked about using the DBCC CHECKIDENT command to see current values as well as to reseed values. This is great if you want to do this one table at a time, but is there another way of doing this for all tables?
Solution
In SQL Server 2005 there are several new catalog views that have been created. One of these new views is sys.identity_columns. This view gives you additional insight to your identity columns, so you can see the columns and values across all tables in your database.
Below is the output you get when you select from this view. In addition to the columns that are returned for the identity column this also uses the sys.columns view as well.
| Column name | Data type | Description |
|---|---|---|
| <columns inherited from sys.columns> | For a list of columns that this view inherits, see sys.columns | |
| seed_value | sql_variant | Seed value for this identity column. The data type of the seed value is the same as the data type of the column itself. |
| increment_value | sql_variant | Increment value for this identity column. The data type of the seed value is the same as the data type of the column itself. |
| last_value | sql_variant | Last value generated for this identity column. The data type of the seed value is the same as the data type of the column itself. |
| is_not_for_replication | bit | Identity column is declared NOT FOR REPLICATION. |
(Source SQL Server 2005 Books Online)
To execute the command for all tables in your database you can run the following command:
SELECT OBJECT_NAME(OBJECT_ID) AS TABLENAME, |
When this command is run against the AdventureWorks database we get the following output.
From this output we can see the name of the table, column that is an identity column, the seed value, increment value, the not_for_replication setting as well as the last value used in the table. This is a nice simple way to retrieve these values and since the data is returned as a table we can easily pull this data into an application.
Next Steps
- Here is another simple catalog view that gives you a wealth of information about your identity columns. See how you can incorporate this into your management procedures or applications.
- Check out these other tips that refer to identity columns
About the author
Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of MSSQLTips.com. Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
