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_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.'?
HariHi,
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@.microsoft.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_CP1_CI_AS NU
LL ,
> [opId] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [comments] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL ,
> [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|||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@.microsoft.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_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_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@.microsoft.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_CP1_CI_AS NU
LL ,
> [opId] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [comments] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL ,
> [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
>
>