List of Identity Columns in Database

Question

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 1

Have a look in BOL for OBJECTPROPERTY and COLUMNPROPERTY

/Kenneth

jshorts SSC Enthusiast Points: 161 More actions · Answer 2

select * from syscolumns where autoval is not null

will list the columns, but you need to know what you are doing if you want to add tablenames, etc

jdlmail1@pocketjoshua.com SSC Journeyman Points: 99 More actions · Answer 3

This came up yesterday in another forum.

Simple example from BOL:

SELECT COLUMNPROPERTY( OBJECT_ID('authors'),'au_lname','IsIdentity')

A query on the Information Schema Views for all identity columns:

SELECT

OBJECT_ID(col.Table_Name) AS [Object_ID]

, COLUMNPROPERTY(

OBJECT_ID(col.Table_Name)

, col.Column_Name,'IsIdentity'

) AS [IsIdentity]

, *

FROM

Information_Schema.Columns col

WHERE

COLUMNPROPERTY(

OBJECT_ID(col.Table_Name)

, col.Column_Name,'IsIdentity') = 1

Good luck,

Joshua

Satish-167844 Valued Member Points: 69 More actions · Answer 4

Thank you for sending the response. I am getting the list of columns. And i am trying to get the Seed value and increments values for the columns.

Regards

Satish

winash SSChampion Points: 11446 More actions · Answer 5

these can be obtained from IDENT_INCR and IDENT_SEED

SELECT

Table_Name,

Column_Name,

IDENT_SEED(Table_Name) Seed,

IDENT_INCR(Table_Name) Incr

FROM INFORMATION_SCHEMA.COLUMNS

WHERE

COLUMNPROPERTY (OBJECT_ID(Table_Name),Column_Name,'IsIdentity') = 1