Detect Identity field? - 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

Transact-SQL (2000)

Detect Identity field?

Author

Topic

robg69
Starting Member

41 Posts

Posted - 2006-04-14 : 13:43:51

I was using the information_Schema views to write some dynamic queries and was wondering if any of them or something else would tell me if field in one of my tables was set to "identity". I've looked at
information_schema.tables, columns, key_column_usage and several sys tables but I haven't found anything. I figured it would probably be quicker just to ask here. So anyone?

Thanks!

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-04-14 : 14:55:24

Stored procedure sp_help tells you which column in a table is identity.

Looking at the code from that proc, you might come up with this:



select
	IDENTITY_COLUMNS =
	b.name+'.'+a.name
from
	syscolumns a
	join
	sysobjects b
	on a.id = b.id
where
	a.colstat & 1 = 1	and
	b.type = 'u'
order by
	1

There is also this:


select
	*
from
	INFORMATION_SCHEMA.COLUMNS a
where
	columnproperty(object_id(a.TABLE_SCHEMA+'.'+a.TABLE_NAME),a.COLUMN_NAME,'IsIdentity') = 1

CODO ERGO SUM

robg69
Starting Member

41 Posts

Posted - 2006-04-14 : 15:04:24

Cool, I totally forgot about sp_help. Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources