Altering the identity seed of a table

Hi,

Im trying to alter the identity seed of a table in a script and I cant work out how to do so without doing it the way Enterprise Manager does it - ie create a tmp table with the new id, populate it with data and set constraints etc, then drop the original table and rename the tmp one.

This is pretty hard to script for arbitrary tables automatically, so I was wondering if there is some way to do it with an ALTER TABLE script?

cheers
Pete StoreyDBCC Checkident.|||Thanks!

ALTER TABLE ... IDENTITY question....

Hi,
I am trying to programatically change the seed of an existing IDENTITY
column (Copy_ID). When I run the following command I get the error:
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'IDENTITY'.
ALTER TABLE Copy ALTER COLUMN Copy_ID Int IDENTITY (1,1);
Where am I going wrong?
Thanks in advance,
StuCheck out DBCC CHECKIDENT in the BOL.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Stu" <s.lock@.cergis.com> wrote in message
news:uGBp9R1RGHA.5500@.TK2MSFTNGP12.phx.gbl...
Hi,
I am trying to programatically change the seed of an existing IDENTITY
column (Copy_ID). When I run the following command I get the error:
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'IDENTITY'.
ALTER TABLE Copy ALTER COLUMN Copy_ID Int IDENTITY (1,1);
Where am I going wrong?
Thanks in advance,
Stu

Alter Seed value of Identity column

Hi,
I want to create temporary table, say "a" which has a column say "col1"
which i wnt to be an identity for which I need to provide a seed value.
I tried the following
1. Create a Table with Identity seed,value as (1,1)
2. Tried to alter the table using "alter table a alter column col1
IDENTITY (500,1)" but this fails saying that "Server: Msg 156, Level 15,
State 1, Line 1 Incorrect syntax near the keyword 'IDENTITY'."
Any idea how to do this
Note: Since it is a temporary table I can't create a dynamic query bcoz the
table will be in tht context and later on will be destroyed (this is wht I
observed, correct me if I am wrong )
TIA
Thnx
PSee DBCC CHECKINDENT command in SQL Server Books Online.
Anith|||Thnx it worked !!!
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:%231WOwn90GHA.720@.TK2MSFTNGP02.phx.gbl...
> See DBCC CHECKINDENT command in SQL Server Books Online.
> --
> Anith
>

Alter Seed value of Identity column

Hi,
I want to create temporary table, say "a" which has a column say "col1"
which i wnt to be an identity for which I need to provide a seed value.
I tried the following
1. Create a Table with Identity seed,value as (1,1)
2. Tried to alter the table using "alter table a alter column col1
IDENTITY (500,1)" but this fails saying that "Server: Msg 156, Level 15,
State 1, Line 1 Incorrect syntax near the keyword 'IDENTITY'."
Any idea how to do this
Note: Since it is a temporary table I can't create a dynamic query bcoz the
table will be in tht context and later on will be destroyed (this is wht I
observed, correct me if I am wrong :) )
TIA
Thnx
PSee DBCC CHECKINDENT command in SQL Server Books Online.
--
Anith|||Thnx it worked !!!
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:%231WOwn90GHA.720@.TK2MSFTNGP02.phx.gbl...
> See DBCC CHECKINDENT command in SQL Server Books Online.
> --
> Anith
>

Alter identity -field?

Hello.
I have a table with int identity field (X INT IDENTITY(1,1)).
I want update SEED value to 4000.
I cannot drop column because I have a foreign key to it from other table.
How I can do this (update/alter identity's SEED value to column)?
dbcc checkident
"Major" <lievonen@.jyu.fi.HALOOOOOOOO> wrote in message
news:OsdhknwxEHA.2540@.TK2MSFTNGP15.phx.gbl...
> Hello.
> I have a table with int identity field (X INT IDENTITY(1,1)).
> I want update SEED value to 4000.
> I cannot drop column because I have a foreign key to it from other table.
> How I can do this (update/alter identity's SEED value to column)?
>
|||Hi,
Execute the below command, replace the dbname and table name with actual
USE DBNAME
GO
DBCC CHECKIDENT (tablename, RESEED, 4000)
Thanks
Hari
SQL Server MVP
"Major" <lievonen@.jyu.fi.HALOOOOOOOO> wrote in message
news:OsdhknwxEHA.2540@.TK2MSFTNGP15.phx.gbl...
> Hello.
> I have a table with int identity field (X INT IDENTITY(1,1)).
> I want update SEED value to 4000.
> I cannot drop column because I have a foreign key to it from other table.
> How I can do this (update/alter identity's SEED value to column)?
>