Retrieve identity column properties for SQL Server database tables

By:   |   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_IDAS TABLENAME,
         
NAME AS COLUMNNAME,
         
SEED_VALUE,
         
INCREMENT_VALUE,
         
LAST_VALUE,
         
IS_NOT_FOR_REPLICATION
FROM     SYS.IDENTITY_COLUMNS
ORDER BY 1

When this command is run against the AdventureWorks database we get the following output.

proble1

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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Greg Robidoux 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



Comments For This Article




Wednesday, February 27, 2013 - 12:19:43 AM - K.Padmavathi Back To Top (22439)

hi

i want to select the tables which are seeded from database creation.can any one help to create it using cursors

seed tables means the tables which have the default data when a database is created

 

please tell me its urgent















get free sql tips
agree to terms