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

Why not using an alter table?

alter table TestTable
alter column id int

http://geertverhoeven.blogspot.com

That would not drop the identity property of the column.

two questions for you....

Why would you want to change the system tables?
Why would the manufacturer of the database want to allow you to modify the system tables?