SQL Server 2005 determine if table has identity column

tegronakron
tegronakron used Ask the Experts™
on
I am writing a query to determine if a table has an identity column. If so I want to turn on IDENTITY INSERT ON. This query worked in my SQL Server 2000 database, but i recently restored the database to SQL Server 2005.
IF (SELECT COUNT(*) FROM syscolumns c, sysobjects o where o.id = c.id and c.autoval is not null and o.name='tblDebug') > 0 SET IDENTITY_INSERT tblDebug ON
ELSE
SET IDENTITY_INSERT tblDebug OFF

Has this information changed locations in SQL Server 2005, or did it never get created because I simply restored this database to SQL 2005?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
It worked fine for me. What error message is it returning?

Commented:
The autoval is NULL.
That is an internal column to SQL Server.

IF (SELECT COUNT(*) FROM sys.syscolumns c, sys.sysobjects o
where o.id = c.id  and o.name='tblDebug') > 0 BEGIN
SET IDENTITY_INSERT tblDebug ON
PRINT 'ID set on' END
ELSE
BEGIN
SET IDENTITY_INSERT tblDebug OFF
PRINT 'ID set off'
END
Commented:
IF (SELECT IDENT_CURRENT('holiday') )> 0 BEGIN
SET IDENTITY_INSERT holiday ON
PRINT 'ID set on' END
ELSE
BEGIN
SET IDENTITY_INSERT holiday OFF
PRINT 'ID set off'
END

Try this instead

Author

Commented:
Thanks it worked!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial