Home > Catalog Views, SQLServer, SQLServer 2005, SQLServer 2008, SQLServer 2008 R2 > Using Catalog Views – sys.identity_columns

Using Catalog Views – sys.identity_columns

sys.identity_columns contains a list of all identity columns in the database, some of the columns returned by sys.identity_columns are as below:

1. Object_id – object id of the parent table,

2. Name – column name for the identity column,

3. Column_id – id of the columns in the table,

4. Is_identity – 1,

5. Seed_value – Initial seed valued for the identity column,

6. Increment_value – Increment value specified for the column,

7. Last_value – last identity value that was used for the table,

8. Is_not_for_replication – 1, if the column was created with NOT FOR REPLICATION,

 

It can be used as:

— © 2011 – Vishal (http://SqlAndMe.com)

 

SELECT object_id, name, column_id, is_identity,

       seed_value, increment_value, last_value, is_not_for_replication

FROM   sys.identity_columns

Partial Result Set:

image

 

To retrieve table name, it can be joined with sys.tables or OBJECT_NAME() function can be used as below:

SELECT OBJECT_NAME(object_id) AS 'Table Name', name, column_id, is_identity,

       seed_value, increment_value, last_value, is_not_for_replication

FROM   sys.identity_columns

Partial Result Set:

image

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: