Retrieve identity column properties for SQL Server database tables


By:   |   Updated: 2007-06-29   |   Comments (1)   |   Related: More > 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.

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



Last Updated: 2007-06-29


get scripts

next tip button



About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

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



download





Recommended Reading

Add or drop identity property for an existing SQL Server column

Populate a SQL Server column with a sequential number not using an identity

Auto generated SQL Server keys with the uniqueidentifier or IDENTITY

SQL Server IDENTITY System Function Comparison

Insert missing SQL Server IDENTITY column values using SET IDENTITY_INSERT command








get free sql tips
agree to terms


Learn more about SQL Server tools