mssqltips logo

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




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





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

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

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools