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
reconfigure with override
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'
exec sp_configure 'allow update', 0
reconfigure with override

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?

Sandeep Saran