Searching for identity... - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

Searching for identity...

Author

Topic

monkeybite
Posting Yak Master

152 Posts

Posted - 2004-07-30 : 17:32:03

Anyone know of a way to find all identity columns in a database?

INFORMATION_SCHEMA.Columns doesn't seem to indicate them in any way. Looking at syscolumns, seems that the autoval column might be the one to check.

Thanks

~ monkey

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-07-30 : 17:50:26

select o.name, c.name
from sysobjects o
inner join syscolumns c
on o.id = c.id
where columnproperty(o.id, c.name, 'IsIdentity') = 1
order by o.name

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-07-30 : 17:52:29

No join needed:

SELECT OBJECT_NAME(id) AS TableName, name AS ColumnName
FROM syscolumns
WHERE COLUMNPROPERTY(id, name, 'IsIdentity') = 1
ORDER BY OBJECT_NAME(id)

Tara

monkeybite
Posting Yak Master

152 Posts

Posted - 2004-07-30 : 17:53:42

Nice. forgot about that function COLUMNPROPERTY(). Thanks!

~ monkey

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources