I need to remove the identity property on a column

Question

Post reply

I need to remove the identity property on a column

reg-653649

Valued Member

Points: 61
More actions
February 25, 2008 at 7:16 am

#69608

Here is the table definition...
CREATE TABLE [dbo].[tblObject](
[ObjectID] [int] IDENTITY(1,1) NOT NULL,
[ObjectDataDefinition] [text] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
I really need to get rid of the IDENTITY property of column ObjectID, without dropping the Column. I SQL Management studio I manage to do this by removing "ObjectID" in the table property tab under "Table designer + Identity Column" and when I save everything is ok.
However I need to do this using SQL code...
ALTER TABLE tblObject ALTER COLUMN ObjectID int NOT NULL, does not remove the IDENTITY property...
Any suggestions?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323506 More actions · Answer 1

behind the scenes, the GUI is creating a new table, then dropping the constraints on the old table,

dropping the table, renaming the new table to the proper name, and then adding the constraints;

there is a button to see the script, and you can simply copy that script to what it was doing.

It does that because you can't alter the column to drop the identity; it has to be rebuilt.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Todd Engen SSCrazy Eights Points: 9869 More actions · Answer 2

Open up SQL Profiler and use the Replay trace template, filtering on your DatabaseID, before you change your identity column in EM or SSMS. That will show you how those tools are doing that for you.

Read this article for more options: Changing a Non-IDENTITY column to IDENTITY and vice versa

Steve Jones - SSC Editor SSC Guru Points: 734542 More actions · Answer 3

You can hack the system tables if you like to remove the Identity value. An MVP provided this script:

use master

-- drop database test

Create database Test

Use test

Create table exemplo ( idx int identity(1,1), idx2 int )

insert into exemplo (idx2) Values (1)

insert into exemplo (idx2) Values (2)

insert into exemplo (idx2) Values (3)

Go

sp_configure 'allow updates', 1

Go

Reconfigure With Override

Go

Update Syscolumns Set typestat = 0 where name = 'idx'

Update Syscolumns Set colstat = 0 where name = 'idx'

There is also a Connect item to allow this, since it should be easy. You can vote here: http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=252226

Paul Paiva SSCrazy Points: 2237 More actions · Answer 4

When I try setting 'allow updates' to a 1, I get the following message:

Msg 259, Level 16, State 1, Line 1

Ad hoc updates to system catalogs are not allowed.

So it seems like system updates are disabled in SQL 2008. Has anyone worked around this?

- Paul

http://paulpaivasql.blogspot.com/

Lowell SSC Guru Points: 323506 More actions · Answer 5

since you posted the question in a SQL 2000 forum, that's what we assumed you were using.

it is not possible to do that in 2005 or 2008; it was a 2000 only hack.

you must do it the hard way, renaming the table, recreating the table without the identity, moving the data, restoring any foreign keys, and then dropping the old table...

like i said, I usually let the GUI do all the heavy lifting when it coimes to a rebuild like that.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!