SQL Server Performance Forum – Threads Archive

How to find IDENTITY column?

How to write the SQL Statement to find out a particular column is IDENTITY column or not? If yes what is the IDENTITY Seed and IDENTITY increment for that column?
Which system tables and how we can relate those?
Surendra Kalekar

sp_help ‘TableName’ will tell you This will list all the tables with Identity columns SELECT table_name, column_name as Identity_Column
FROM information_schema.columns
WHERE COLUMNPROPERTY(OBJECT_ID(table_name),column_name,’IsIdentity’)=1
order by table_name Madhivanan Failing to plan is Planning to fail

In addition have a look at IDENT_SEED and IDENT_INCR in BOL. Btw, you can also run
USE Northwind
SELECT IDENTITYCOL
FROM Orders if there is an IDENTITY column on that table, it will be returned. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

But If the table doesnt have identity column, it will give error
I wonder why the result is error instead of returing Null Madhivanan Failing to plan is Planning to fail

Dunno. [email protected] [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] I thought surendrakalekar wanted to find out *what* column is the IDENTITY column, not *if* there is one present. – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Ich unterstÃ¼tze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)

SELECT table_name, column_name as Identity_Column,ident_incr(table_name),ident_seed(table_name) incre
FROM information_schema.columns
WHERE COLUMNPROPERTY(OBJECT_ID(table_name),column_name,’IsIdentity’)=1
order by table_name will give you the IDENTITY Seed and IDENTITY increment for that column?
just and addition to madhivanan code —————————————-
Cast your vote
http://www.geocities.com/dineshasanka/sqlserver05.html http://spaces.msn.com/members/dineshasanka

From EM,
Right clicking the table and select design table.
Goto table properties. and there you will find table identity column name if any.

>>just and addition to madhivanan code Thanks for the addition [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Madhivanan Failing to plan is Planning to fail

thanks everybody
Surendra Kalekar

]]>