DROP IDENTITY ?

Is there a way to drop the Identity property of a column in a table using an
Alter Table?
I would like to do it in Query Analyzer, not EM, and I do not want to have
to create a new table and copy the data to the new table (This is how EM
performs this operation.)
I'm looking for some kind of ALTER TABLE or a system procedure. Is it
possible?
(BTW - I don't like the idea of changing the STATUS bit in syscolumns with
an UPDATE syscolumns T-SQL either.)
Thanksdrop the column and recreate it without identity property.
Ex:
create table i(i int not null identity, ii varchar(6000))
go
alter table i drop column i
go
alter table i add i int
go
Note: you can explicitly insert the values into identity column of a table by doing session level
setting of IDENTITY_INSERT.
Ex:
SET IDENTITY_INSERT <table> ON
- Vishal|||You can drop the column if you don't care about losing the
data, otherwise you may need to use EM.
Edgardo Valdez
MCSD, MCDBA, MCSE, MCP+I
http://www.edgardovaldez.us/
>--Original Message--
>Is there a way to drop the Identity property of a column
in a table using an
>Alter Table?
>I would like to do it in Query Analyzer, not EM, and I do
not want to have
>to create a new table and copy the data to the new table
(This is how EM
>performs this operation.)
>I'm looking for some kind of ALTER TABLE or a system
procedure. Is it
>possible?
>(BTW - I don't like the idea of changing the STATUS bit
in syscolumns with
>an UPDATE syscolumns T-SQL either.)
>Thanks
>
>.
>|||cw3,
There is no option in the ALTER TABLE command to drop an IDENTITY property.
If you want to preserve the data in the identity column, include a copy
step. Here's a slight modification of Vishal's solution:
create table MyTable (i int not null identity, ii varchar(6000))
go
insert MyTable values ('X')
insert MyTable values ('Y')
go
alter table MyTable add i2 int
go
update MyTable set i2 = i
go
alter table MyTable drop column i
go
alter table MyTable add i int
go
update MyTable set i = i2
go
alter table MyTable drop column i2
The main disadvantage of this approach is that the column order may not be
what you wanted, because ALTER TABLE puts new columns at the end.
To control column order, you need to do something like what Enterprise
Manager does. You can get the EM script and tweak it yourself before
applying it.
Ron
--
Ron Talmage
SQL Server MVP
"cw3" <cw@.3mc.com> wrote in message
news:ugM9gSEjDHA.1964@.TK2MSFTNGP12.phx.gbl...
> Is there a way to drop the Identity property of a column in a table using
an
> Alter Table?
> I would like to do it in Query Analyzer, not EM, and I do not want to have
> to create a new table and copy the data to the new table (This is how EM
> performs this operation.)
> I'm looking for some kind of ALTER TABLE or a system procedure. Is it
> possible?
> (BTW - I don't like the idea of changing the STATUS bit in syscolumns with
> an UPDATE syscolumns T-SQL either.)
> Thanks
>