View Single Post
 
Old February 20th, 2007, 06:59 AM
sandeep sandeep is offline
Authorized User
 
Join Date: Jun 2003
Location: Indore, M.P., India.
Posts: 19
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to sandeep
Default dropping the identity property of column

Hi All,

I need to write a SQL script to drop the identity property of the column without dropping the column itself. In SQL Server 2000 this can be achieved as

sp_configure 'allow update', 1
GO
reconfigure with override
GO
update syscolumns set colstat = colstat - 1 /*turn off bit 1 which indicates it's an identity column */
where id = object_id('test')
and name = 'col1'
go
exec sp_configure 'allow update', 0
go
reconfigure with override
go

In SQL Server 2005 this script fails as it does not allow direct updation to system tables. Does anybody know of any workaround for this?

Regards,
Sandeep Saran