Developer Forum

Hi all

I need some Help in creating a SQL Script which I can Run direct on MS SQL
2000 Server.

Of existing tables I want to change the:
- Identity
- Identity Seed
- Identity Increment

I already tried something with ALTER TABLE, but I guess I did something
wrong, did not work sofar.
Would be glad if someone could give me an example of how to do this.

Thanks for the help

Cheers
Marcel

Marcel

1) What do you mean "change indentity"? You can drop the column defined as
an IDENTITY property

2)DBCC CHECKIDENT

3) DROP column and re-create with a new Increment

You can change the identity seed with dbcc checkident (you can see
more details in BOL). I don't know of any supported way to modify
the identity increment.

Adi

With change I mean:
For Table X, the Collumn Name with the Primary Key, has Identity set to No

For this Collumn I want to Set the Identity to:Yes, with Identity Seed = 0
and Identity Increment = 1.
Because I have to do this more then once for several Tables, I do not want
to do this manually but through a Script.

The thing is: do not have a clue how to do it.

Cheers
Marcel


"Uri Dimant" < XXXX@XXXXX.COM > schrieb im Newsbeitrag

Marcel

You cannot do that

CREATE TABLE Test (c INT NOT NULL)

INSERT INTO Test VALUES (1)
INSERT INTO Test VALUES (2)

---Want to add an IDENTITY Property

ALTER TABLE Test ADD c1 INT NOT NULL IDENTITY(1,1)
GO
ALTER TABLE Test DROP COLUMN c
GO
sp_rename 'Test.c1','c','column'
GO
SELECT * FROM Test

DROP TABLE Test

Thanks for the Answer

I will try that with ALTER TABLE

Cheers
Marcel

"Uri Dimant" < XXXX@XXXXX.COM > schrieb im Newsbeitrag