How to get Identity attributes of a Column in TSQL | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

How to get Identity attributes of a Column in TSQL

I want to get the Identity Seed attribute of a given column by writing a simple SQL Query.
Can someone please help me with this? Thanks, Bijesh

sp_help tableName See the third resultset
Madhivanan Failing to plan is Planning to fail
Check out IDENT_SEED() in BOL. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
SELECT IDENT_SEED(‘table_or_view’) Just insert the name of the table – any table can have only one identity column, so you don’t need to know the name of the column. And before you ask, the increment value can be retrieved like this: SELECT IDENT_INCR(‘table_or_view’)
Thanks for all your help.
My question, I had asked may be wrong, but what I really want is not the Identity value that’s stored in the column but instead I want to know if the column has an Identity seed defined on it and if so by what value is it going to increment? Its like I want to know the column definition using a TSQL query. Does SQL store this information in any of its System Tables? Thanks, Bijesh
This lists the tables that have identity column SELECT object_name(id) as table_name, name
FROM syscolumns
WHERE columnproperty(id, name, ‘IsIdentity’) = 1
order by 1 Madhivanan Failing to plan is Planning to fail
select sc.name from syscolumns sc inner join sysobjects so
on sc.id=so.id
where so.name=’table_name’ and colstat = 1
select ident_seed(‘table_name’)
select ident_incr(‘table_name’)

For a given table, that is exactly what IDENT_SEED and IDENT_INCR will tell you. For a given column, check whether syscolumns.colstat = 1, like this: select so.[name], sc.[name], sc.colstat
from dbo.syscolumns sc
inner join dbo.sysobjects so on sc.[id] = so.[id]
where sc.[name] = ‘identitycolumn’
and so.[name]= ‘tablename’
You can combine them in single query
SELECT object_name(id) as table_name, name ,
ident_seed(object_name(id)) as Identity_seed,ident_incr(object_name(id)) as increment
FROM syscolumns
WHERE columnproperty(id, name, ‘IsIdentity’) = 1
order by 1
Madhivanan Failing to plan is Planning to fail
Thank you all. I guess, Madhivanan’s resolution will help me to a greater extent in my task. Thanks once again for all your help and suggestions. Thanks, Bijesh
]]>