Querying for identity columns in a table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Querying for identity columns in a table

Author

Topic

Arithmomaniac
Yak Posting Veteran

66 Posts

Posted - 2008-07-24 : 13:15:11

I want to set a sproc to create an identity column in a table, but only if it does not have one.

What's the best IF EXISTS() statement to check for an identity column if you feed it a table name? I can't find the sysview linking tables to
columns.

Thank you,
Arithmomaniac

---------
Ignorance may be bliss, but knowledge is thrill.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-24 : 13:19:08

select COLUMN_NAME, TABLE_NAME
from INFORMATION_SCHEMA.COLUMNS
where COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') =
1

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-25 : 03:29:55

or

select name from sys.identity_columns
where object_id=object_id('table_name')

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources