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_C
P1_CI_AS NULL
 ,
[opId] [nvarchar] (50) COLLATE  SQL_Latin1_General_C
P1_CI_AS NULL ,
[comments] [nvarchar] (64) COLLATE  SQL_Latin1_General_C
P1_CI_AS NULL
 ,
& #91;eventProperties]
 [nvarchar] (1024) COLLATE  SQL_Latin1_General_C
P1_C
I_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 need to drop and create the table to remove IDENTITY property.

If you use Enterprise manager it internally copy the data outside,script the
schema, modify the script to remove identity
and create the table back and load data.

Thanks
Hari
SQL Server MVP



"Hari" <Hari@discussions.microsoft.com> wrote in message
news:F8F07062-110A-4E07-8526- 5D93E3B6C063@microso
ft.com...
> 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'IsUserTabl
e') = 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_C
P1_CI_AS NU
LL ,
> [opId] [nvarchar] (50) COLLATE  SQL_Latin1_General_C
P1_CI_AS NULL ,
> [comments] [nvarchar] (64) COLLATE  SQL_Latin1_General_C
P1_CI_AS NU
LL ,
> & #91;eventProperties]
 [nvarchar] (1024) COLLATE  SQL_Latin1_General_C
P1
_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

Here's the script:

ALTER TABLE dbo.EventLog ADD NewRecordID int NULL
GO
UPDATE dbo.EventLog SET NewRecordID = RecordId
GO
ALTER TABLE dbo.EventLog DROP CONSTRAINT PK_EventLog
GO
ALTER TABLE dbo.EventLog DROP COLUMN RecordId
GO
EXEC sp_rename 'EventLog.NewRecordID', 'RecordId'
GO
ALTER TABLE dbo.EventLog ALTER COLUMN RecordId int NOT NULL
GO
ALTER TABLE dbo.EventLog ADD CONSTRAINT PK_EventLog PRIMARY KEY CLUSTERED
(RecordId) ON [PRIMARY]
GO

Make sure there's nobody making changes to this data while you are running
this script.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @ http://vyaskn.tripod.com/


"Hari" <Hari@discussions.microsoft.com> wrote in message
news:F8F07062-110A-4E07-8526- 5D93E3B6C063@microso
ft.com...
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_C
P1_CI_AS NULL
 ,
[opId] [nvarchar] (50) COLLATE  SQL_Latin1_General_C
P1_CI_AS NULL ,
[comments] [nvarchar] (64) COLLATE  SQL_Latin1_General_C
P1_CI_AS NULL
 ,
& #91;eventProperties]
 [nvarchar] (1024) COLLATE  SQL_Latin1_General_C
P1_C
I_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

Thank you verymuch for your suggetion.

Hari


"Narayana Vyas Kondreddi" wrote:

> Here's the script:
>
> ALTER TABLE dbo.EventLog ADD NewRecordID int NULL
> GO
> UPDATE dbo.EventLog SET NewRecordID = RecordId
> GO
> ALTER TABLE dbo.EventLog DROP CONSTRAINT PK_EventLog
> GO
> ALTER TABLE dbo.EventLog DROP COLUMN RecordId
> GO
> EXEC sp_rename 'EventLog.NewRecordID', 'RecordId'
> GO
> ALTER TABLE dbo.EventLog ALTER COLUMN RecordId int NOT NULL
> GO
> ALTER TABLE dbo.EventLog ADD CONSTRAINT PK_EventLog PRIMARY KEY CLUSTERED
> (RecordId) ON [PRIMARY]
> GO
>
> Make sure there's nobody making changes to this data while you are running
> this script.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @ http://vyaskn.tripod.com/
>
>
> "Hari" <Hari@discussions.microsoft.com> wrote in message
> news:F8F07062-110A-4E07-8526- 5D93E3B6C063@microso
ft.com...
> 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'IsUserTabl
e') = 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_C
P1_CI_AS NU
LL ,
> [opId] [nvarchar] (50) COLLATE  SQL_Latin1_General_C
P1_CI_AS NULL ,
> [comments] [nvarchar] (64) COLLATE  SQL_Latin1_General_C
P1_CI_AS NU
LL ,
> & #91;eventProperties]
 [nvarchar] (1024) COLLATE  SQL_Latin1_General_C
P1
_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
>
>
>