2

I want to remove identity from a column by updating it like this..

sp_configure 'allow update', 1 
go 

reconfigure with override 
go

update sys.syscolumns 
set colstat = 0    -- turn off bit 1 which indicates identity column 
where id = object_id('tbl1') and name = 'ids'
go

sp_configure 'allow updates', 0 
go 

reconfigure 
go

I am getting this error, tried many times.

Msg 259, Level 16, State 1, Line 15
Ad hoc updates to system catalogs are not allowed.

Arulkumar
  • 12,966
  • 14
  • 47
  • 68
Daniyal Tariq
  • 87
  • 1
  • 2
  • 14
  • There are lots of suggested solutions here: https://www.google.com/search?sourceid=navclient&aq=&oq=Ad+hoc+updates+to+system+catalogs+are+not+allowed.&ie=UTF-8&rlz=1T4GGNI_enUS551US551&q=Ad+hoc+updates+to+system+catalogs+are+not+allowed.&gs_l=hp...0j0i22i30.0.0.1.209060...........0.wph1AZZRtto – Tab Alleman Dec 17 '15 at 13:50
  • 1
    Well - the error says it all and very clearly: **updates to system catalogs are not allowed** - those are **system-level** catalog views - you have no business fiddling around in them and trying to update stuff - these views reflect the reality of your system - if you want to change something - change it in the respective database object (here: in `tbl1`) - not in the system catalog view! – marc_s Dec 17 '15 at 13:55
  • 1
    Seriously, doing this will likely just break something. Just [drop and recreate the table](http://stackoverflow.com/questions/8230257/sql-server-remove-identity-from-a-column-in-a-table). – Bacon Bits Dec 17 '15 at 15:08
  • I want to do it any way I simply want to Remove Identity Property from a column. – Daniyal Tariq Dec 17 '15 at 17:08

1 Answers1

1

If you want to get rid of it completely, just rename the table and then dump the data into a new table.

EXEC sp_rename 'OriginalTblName','OLD_OriginalTblName'

CREATE TABLE OriginalTblName (Definition of your Table)

INSERT OriginalTblName
SELECT * FROM OLD_OriginalTblName

You can skip the CREATE TABLE step if you want by just selecting the contents into the new table. You lose the ability to define the fields the way you want with this method.

SELECT * FROM OLD_OriginalTblName
INTO OriginalTblName

If you are just wanting to INSERT new records, you can use IDENTITY INSERT to insert the records you want. Just be careful not to duplicate the values or you will break the table.

SET IDENTITY_INSERT ON OriginalTblName

INSERT OriginalTblName
SELECT someFields
FROM someTbl

SET IDENTITY_INSERT OFF OriginalTblName

IDENTITY INSERT will not work for UPDATE on the IDENTITY field. You will need to capture the data and reinsert the record with one of the methods described above.

Steve Mangiameli
  • 688
  • 7
  • 15