Archive

Posts Tagged ‘sys.identity_columns’

Using Catalog Views – sys.identity_columns

21.06.2011 No comments

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