How can i remove IDENTITY by ALTER TABLE?

Hello!
Can i remove IDENTITY property of the column by ALTER TABLE command?
Thanks!
Best regards, Konstantin KnyazevThis is the script that is generated when you do this with Enterprise
Manager
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_blah
(
oldid int NOT NULL
) ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.blah)
EXEC('INSERT INTO dbo.Tmp_blah (oldid)
SELECT oldid FROM dbo.blah TABLOCKX')
GO
DROP TABLE dbo.blah
GO
EXECUTE sp_rename N'dbo.Tmp_blah', N'blah', 'OBJECT'
GO
COMMIT
As you can see it's not a one-liner
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Here's an example, basically you can't remove the property without an
intermediary column...
Note, you'll need to deal with the constraint / index if you created one,
just use ALTER TABLE DROP CONSTRAINT and add it again.
use tempdb
create table mytest (
id int not null identity,
myothercol int
)
insert mytest ( myothercol ) values( 1 )
go
begin tran
alter table mytest add id_new int null
exec ( 'update mytest set id_new = id' )
exec ( 'alter table mytest drop column id' )
exec sp_rename 'mytest.id_new', id
commit tran
go
select *
from mytest
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Konstantin Knyazev" <kknyazev_no_spam_@.mail.ru> wrote in message
news:ODgeTATaGHA.1240@.TK2MSFTNGP03.phx.gbl...
> Hello!
> Can i remove IDENTITY property of the column by ALTER TABLE command?
> Thanks!
> Best regards, Konstantin Knyazev
>|||Yes, i have tested this by EM, but i have too big table and it is very long
operation, about 40-50 min. I'm looking something quicker.
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1146056271.030082.51260@.i40g2000cwc.googlegroups.com...
> This is the script that is generated when you do this with Enterprise
> Manager
> BEGIN TRANSACTION
> SET QUOTED_IDENTIFIER ON
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> SET ARITHABORT ON
> SET NUMERIC_ROUNDABORT OFF
> SET CONCAT_NULL_YIELDS_NULL ON
> SET ANSI_NULLS ON
> SET ANSI_PADDING ON
> SET ANSI_WARNINGS ON
> COMMIT
> BEGIN TRANSACTION
> CREATE TABLE dbo.Tmp_blah
> (
> oldid int NOT NULL
> ) ON [PRIMARY]
> GO
> IF EXISTS(SELECT * FROM dbo.blah)
> EXEC('INSERT INTO dbo.Tmp_blah (oldid)
> SELECT oldid FROM dbo.blah TABLOCKX')
> GO
> DROP TABLE dbo.blah
> GO
> EXECUTE sp_rename N'dbo.Tmp_blah', N'blah', 'OBJECT'
> GO
> COMMIT
>
> As you can see it's not a one-liner
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>|||If your tables are so big and it will be PITA to do it then could you just
use
SET IDENTITY_INSERT table_name ON
do your thing
SET IDENTITY_INSERT table_name OFF
just a thought.
Grant
Who gives a {censored} if I am wrong.
"Konstantin Knyazev" <kknyazev_no_spam_@.mail.ru> wrote in message
news:ODgeTATaGHA.1240@.TK2MSFTNGP03.phx.gbl...
> Hello!
> Can i remove IDENTITY property of the column by ALTER TABLE command?
> Thanks!
> Best regards, Konstantin Knyazev
>|||This table should be a part of updatable distributed view, and using of
IDENTITY is restricted for it.
"Grant" <email@.nowhere.com> wrote in message
news:%23d8ZYKTaGHA.4248@.TK2MSFTNGP05.phx.gbl...
> If your tables are so big and it will be PITA to do it then could you just
> use
> SET IDENTITY_INSERT table_name ON
> do your thing
> SET IDENTITY_INSERT table_name OFF
> just a thought.
> --
> Grant
> Who gives a {censored} if I am wrong.
> "Konstantin Knyazev" <kknyazev_no_spam_@.mail.ru> wrote in message
> news:ODgeTATaGHA.1240@.TK2MSFTNGP03.phx.gbl...
>