Removing IDENTITY from a tabe column

Hi Guys,
I have created a table containg a column with IDENTITY keyword. I have data
in the table. Now i need to alter this table without IDENTITY for that
column. Please help me get a sql script to do this one.
The creation scirpt for the table is :
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[EventLog]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[EventLog];
CREATE TABLE [dbo].[EventLog] (
[RecordId] [int] IDENTITY (1, 1) NOT NULL ,
[eventId] [int] NOT NULL ,
[eventType] [int] NOT NULL ,
[eventDateTime] [datetime] NULL ,
[AimNumber] [int] NULL ,
[eventMsg] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[opId] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[comments] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[eventProperties] [nvarchar] (1024) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
) ON [PRIMARY];
ALTER TABLE [dbo].[EventLog] WITH NOCHECK ADD
CONSTRAINT [PK_EventLog] PRIMARY KEY CLUSTERED
(
[RecordId]
) ON [PRIMARY] ;
Now i need script for altering the just identity column.'?
HariI think
alter table [dbo].[EventLog]
alter column [RecordId] [int]
--
thsi will work
Hari wrote:
> Hi Guys,
> I have created a table containg a column with IDENTITY keyword. I have dat
a
> in the table. Now i need to alter this table without IDENTITY for that
> column. Please help me get a sql script to do this one.
> The creation scirpt for the table is :
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[EventLog]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[EventLog];
> CREATE TABLE [dbo].[EventLog] (
> [RecordId] [int] IDENTITY (1, 1) NOT NULL ,
> [eventId] [int] NOT NULL ,
> [eventType] [int] NOT NULL ,
> [eventDateTime] [datetime] NULL ,
> [AimNumber] [int] NULL ,
> [eventMsg] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [opId] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [comments] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [eventProperties] [nvarchar] (1024) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL
> ) ON [PRIMARY];
> ALTER TABLE [dbo].[EventLog] WITH NOCHECK ADD
> CONSTRAINT [PK_EventLog] PRIMARY KEY CLUSTERED
> (
> [RecordId]
> ) ON [PRIMARY] ;
>
> Now i need script for altering the just identity column.'?
> Hari|||I think
alter table [dbo].[EventLog]
alter column [RecordId] [int]
--
this will work
Hari wrote:
> Hi Guys,
> I have created a table containg a column with IDENTITY keyword. I have dat
a
> in the table. Now i need to alter this table without IDENTITY for that
> column. Please help me get a sql script to do this one.
> The creation scirpt for the table is :
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[EventLog]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[EventLog];
> CREATE TABLE [dbo].[EventLog] (
> [RecordId] [int] IDENTITY (1, 1) NOT NULL ,
> [eventId] [int] NOT NULL ,
> [eventType] [int] NOT NULL ,
> [eventDateTime] [datetime] NULL ,
> [AimNumber] [int] NULL ,
> [eventMsg] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [opId] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [comments] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [eventProperties] [nvarchar] (1024) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL
> ) ON [PRIMARY];
> ALTER TABLE [dbo].[EventLog] WITH NOCHECK ADD
> CONSTRAINT [PK_EventLog] PRIMARY KEY CLUSTERED
> (
> [RecordId]
> ) ON [PRIMARY] ;
>
> Now i need script for altering the just identity column.'?
> Hari|||I think
alter table [dbo].[EventLog]
alter column [RecordId] [int]
--
this will work
Hari wrote:
> Hi Guys,
> I have created a table containg a column with IDENTITY keyword. I have dat
a
> in the table. Now i need to alter this table without IDENTITY for that
> column. Please help me get a sql script to do this one.
> The creation scirpt for the table is :
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[EventLog]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[EventLog];
> CREATE TABLE [dbo].[EventLog] (
> [RecordId] [int] IDENTITY (1, 1) NOT NULL ,
> [eventId] [int] NOT NULL ,
> [eventType] [int] NOT NULL ,
> [eventDateTime] [datetime] NULL ,
> [AimNumber] [int] NULL ,
> [eventMsg] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [opId] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [comments] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [eventProperties] [nvarchar] (1024) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL
> ) ON [PRIMARY];
> ALTER TABLE [dbo].[EventLog] WITH NOCHECK ADD
> CONSTRAINT [PK_EventLog] PRIMARY KEY CLUSTERED
> (
> [RecordId]
> ) ON [PRIMARY] ;
>
> Now i need script for altering the just identity column.'?
> Hari|||Hi,
You can not remove the IDENTITY property using Alter table command.
Only way is export data into a new table using
1. SELECT * INTO newtable from existing table
2. create new table with out identity
3. insert the data from new table
4. drop the existing table and rename the new table using sp_rename
Enterprise manager internally almost does the above steps to remove identity
property.
Thanks
Hari
SQL Server MVP
"sajeevp" <sajeev.padmanabhan@.gmail.com> wrote in message
news:1122522742.838595.315250@.f14g2000cwb.googlegroups.com...
>I think
> --
> alter table [dbo].[EventLog]
> alter column [RecordId] [int]
> --
> this will work
>
>
> Hari wrote:
>|||>I think
> --
> alter table [dbo].[EventLog]
> alter column [RecordId] [int]
> --
> thsi will work
No, it will not. Did you try it?|||that was a wrong post .. sorry|||Thanks for your suggetion.
Hari
"Hari Pra" wrote:

> Hi,
> You can not remove the IDENTITY property using Alter table command.
> Only way is export data into a new table using
> 1. SELECT * INTO newtable from existing table
> 2. create new table with out identity
> 3. insert the data from new table
> 4. drop the existing table and rename the new table using sp_rename
> Enterprise manager internally almost does the above steps to remove identi
ty
> property.
> Thanks
> Hari
> SQL Server MVP
>
>
> "sajeevp" <sajeev.padmanabhan@.gmail.com> wrote in message
> news:1122522742.838595.315250@.f14g2000cwb.googlegroups.com...
>
>