Renaming column

ALTER TABLE ISMPBI CHANGE `PBICCGRFNUM` `PBICCGRFNUM1` BIGINT NOT NULL;
this query to rename column name in my sql but not working in sql server
so what is exact syntax to rename columnSee the syntax for the ALTER TABLE command in the online manual (http://msdn2.microsoft.com/en-us/library/ms190273.aspx)

Btw: don't use the backticks. They are a MySQL "speciality" and do not work with any other DBMS (actually they are not needed in MySQL as well)|||MS SQL SERVER does not support RENAME syntax i cant find the syntax to rename column can any one give me exact syntax to rename column|||The ALTER TABLE documentation links to the sp_rename (http://msdn2.microsoft.com/en-us/library/ms188351.aspx) function:
Changes the name of a user-created object in the current database. This object can be a table, index, column, alias data type
Example B: "renaming a column"|||thanks

here is exact syntax

EXEC sp_rename 'BFMENUDEFINITION.[GROUP]', 'MENUGROUP', 'COLUMN';

'BFMENUDEFINITION== tablename

GROUP == old column

'MENUGROUP'== new columnsql

rename physical files?

Is it possible to rename physical MDF and LDF files? I have been searching
the net for a few hours and I thought I got close with an ALTER DB command.
But I got errors. I think that is only for logical names. Anyhow, here is
what I tried:
alter database pre_8live_hc modify file (name = 'restoredb', newname =
'pre_8live_hc')
alter database pre_8live_hc modify file (name = 'restoredb_log', newname =
'pre_8live_hc_log')
This is what I got back:
Server: Msg 5041, Level 16, State 1, Line 1
MODIFY FILE failed. File 'restoredb' does not exist.
Server: Msg 5041, Level 16, State 1, Line 2
MODIFY FILE failed. File 'restoredb_log' does not exist.
The DB name is pre_8live_hc
Filenames are: (logical / physical)
MDF = NTI_hc_data / d:\program files\microsoft sql
server\msssql\data\restoredb.mdf
LDF = NTI_hc_data_log / d:\program files\microsoft sql
server\msssql\data\restoredb_log.mdf
I have seen some stuff about restore with replace and also attachdb, but
could not get definitive answer. And the commands I tried did not work.
Thanks,
Bryan
You can detach the database, move the physical files (and/or rename them)
and then attach the database. If you can't get that to work, show us the
syntax you are using for sp_attach_db and we'll try to see what's wrong.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"IT Department" <it@.ntihc.com> wrote in message
news:r6amd.10958$Ae.9201@.newsread1.dllstx09.us.to. verio.net...
> Is it possible to rename physical MDF and LDF files? I have been
> searching the net for a few hours and I thought I got close with an ALTER
> DB command. But I got errors. I think that is only for logical names.
> Anyhow, here is what I tried:
> alter database pre_8live_hc modify file (name = 'restoredb', newname =
> 'pre_8live_hc')
> alter database pre_8live_hc modify file (name = 'restoredb_log', newname =
> 'pre_8live_hc_log')
> This is what I got back:
> Server: Msg 5041, Level 16, State 1, Line 1
> MODIFY FILE failed. File 'restoredb' does not exist.
> Server: Msg 5041, Level 16, State 1, Line 2
> MODIFY FILE failed. File 'restoredb_log' does not exist.
> The DB name is pre_8live_hc
> Filenames are: (logical / physical)
> MDF = NTI_hc_data / d:\program files\microsoft sql
> server\msssql\data\restoredb.mdf
> LDF = NTI_hc_data_log / d:\program files\microsoft sql
> server\msssql\data\restoredb_log.mdf
>
> I have seen some stuff about restore with replace and also attachdb, but
> could not get definitive answer. And the commands I tried did not work.
> Thanks,
> Bryan
>
|||Hi,
Follow kelens method. What you did is to rename the Logicalfile name. TO
rename the logical database name you have to be in that database.
Thanks
Hari
SQL Server MVP
"IT Department" <it@.ntihc.com> wrote in message
news:r6amd.10958$Ae.9201@.newsread1.dllstx09.us.to. verio.net...
> Is it possible to rename physical MDF and LDF files? I have been
> searching the net for a few hours and I thought I got close with an ALTER
> DB command. But I got errors. I think that is only for logical names.
> Anyhow, here is what I tried:
> alter database pre_8live_hc modify file (name = 'restoredb', newname =
> 'pre_8live_hc')
> alter database pre_8live_hc modify file (name = 'restoredb_log', newname =
> 'pre_8live_hc_log')
> This is what I got back:
> Server: Msg 5041, Level 16, State 1, Line 1
> MODIFY FILE failed. File 'restoredb' does not exist.
> Server: Msg 5041, Level 16, State 1, Line 2
> MODIFY FILE failed. File 'restoredb_log' does not exist.
> The DB name is pre_8live_hc
> Filenames are: (logical / physical)
> MDF = NTI_hc_data / d:\program files\microsoft sql
> server\msssql\data\restoredb.mdf
> LDF = NTI_hc_data_log / d:\program files\microsoft sql
> server\msssql\data\restoredb_log.mdf
>
> I have seen some stuff about restore with replace and also attachdb, but
> could not get definitive answer. And the commands I tried did not work.
> Thanks,
> Bryan
>
|||Thanks guys. The sp_attach_db worked. I will have to admit I didn't try
just renaming the physical files. For some reason I thought I had tried
that in the past and I kep getting errors saying the file didn't exist, but
now that I think back, I was trying to delete one of two transaction logs
and it wouldn't attach back without both logs.
Thanks a bunch!
Bryan
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OeLusr5yEHA.3408@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Follow kelens method. What you did is to rename the Logicalfile name. TO
> rename the logical database name you have to be in that database.
> --
> Thanks
> Hari
> SQL Server MVP
>
> "IT Department" <it@.ntihc.com> wrote in message
> news:r6amd.10958$Ae.9201@.newsread1.dllstx09.us.to. verio.net...
>

Rename or purge my SQL Server 2000 transaction log file

Gurus,
How do I Rename or purge my SQL Server 2000 transaction log file? I have
successfully renamed the database using the ALTER DATABASE. I have
successfully renamed the physical MDF file by detaching the database,
renaming the file, and bringing it back online. However the transaction log
file still has the same old name. No one will be using the database over
the weekend.
--
Spin> I have successfully renamed the physical MDF file by detaching the
> database, renaming the file, and bringing it back online. However the
> transaction log file still has the same old name.
You can use the same process to rename the transaction log file: detach the
database, rename file(s) as desired and reattach the database specifying
*all* files:
EXEC sp_attach_db 'NewDatabaseName',
'C:\DataFiles\NewDatabaseName.mdf',
'C:\LogFiles\NewDatabaseName_Log.ldf'
When you omit the log file on sp_attach_db, SQL Server reuses the original
log file if it exists.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Spin" <Spin@.spin.com> wrote in message
news:4ecfcgF1e15rgU1@.individual.net...
> Gurus,
> How do I Rename or purge my SQL Server 2000 transaction log file? I have
> successfully renamed the database using the ALTER DATABASE. I have
> successfully renamed the physical MDF file by detaching the database,
> renaming the file, and bringing it back online. However the transaction
> log file still has the same old name. No one will be using the database
> over the weekend.
> --
> Spin
>

Rename or purge my SQL Server 2000 transaction log file

Gurus,
How do I Rename or purge my SQL Server 2000 transaction log file? I have
successfully renamed the database using the ALTER DATABASE. I have
successfully renamed the physical MDF file by detaching the database,
renaming the file, and bringing it back online. However the transaction log
file still has the same old name. No one will be using the database over
the weekend.
Spin> I have successfully renamed the physical MDF file by detaching the
> database, renaming the file, and bringing it back online. However the
> transaction log file still has the same old name.
You can use the same process to rename the transaction log file: detach the
database, rename file(s) as desired and reattach the database specifying
*all* files:
EXEC sp_attach_db 'NewDatabaseName',
'C:\DataFiles\NewDatabaseName.mdf',
'C:\LogFiles\NewDatabaseName_Log.ldf'
When you omit the log file on sp_attach_db, SQL Server reuses the original
log file if it exists.
Hope this helps.
Dan Guzman
SQL Server MVP
"Spin" <Spin@.spin.com> wrote in message
news:4ecfcgF1e15rgU1@.individual.net...
> Gurus,
> How do I Rename or purge my SQL Server 2000 transaction log file? I have
> successfully renamed the database using the ALTER DATABASE. I have
> successfully renamed the physical MDF file by detaching the database,
> renaming the file, and bringing it back online. However the transaction
> log file still has the same old name. No one will be using the database
> over the weekend.
> --
> Spin
>

rename column

i need t-sql syntax for renaming a column.
the column name contains the [] characters.
alter table <tablename> rename [old field name] to NewFieldName
isn't working for me.
tia,
mcnewsxpthis works:
EXEC sp_rename 'MyTable.[Old Field]', 'NewField', 'COLUMN'|||use sp_rename..
I haven't seen the sytax that you have used.|||You might want to check this out ::
http://vadivel.blogspot.com/2004/08...g-sprename.html
Best Regards
Vadivel
http://vadivel.blogspot.com
"mcnews" wrote:

> i need t-sql syntax for renaming a column.
> the column name contains the [] characters.
> alter table <tablename> rename [old field name] to NewFieldName
> isn't working for me.
> tia,
> mcnewsxp
>sql

Removing Identity Property

Folks,
I am trying to alter my database thru SQL Scripts. I have a few tables where
I have columns having Identity Property. I now want to modify these columns
having no Identity Property. But I can't find SQL Statement that can do
this.
Can some one help?
Regards
Shailaindra SharmaYou cannot remove this property... you must add another column, move the id
info to the new column, drop the old column, and rename the new column...
Or do the same thing at the table level... However,
If you go into SEM, select the table , right click and go into design, you
can simply select the column and uncheck Identity at the bottom... SEM will
generate a script to do this work for you - if there are lots of rows it may
take a while.
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Shailaindra Sharma" <shailaindra@.abosoftware.com> wrote in message
news:eIHMP%23pZFHA.3840@.tk2msftngp13.phx.gbl...
> Folks,
> I am trying to alter my database thru SQL Scripts. I have a few tables
where
> I have columns having Identity Property. I now want to modify these
columns
> having no Identity Property. But I can't find SQL Statement that can do
> this.
> Can some one help?
> Regards
> Shailaindra Sharma
>|||Why can't we do things like that which we can easily do in access. How hard
can it be for ms to add these things which will make life so easy.
Regards
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:ewlf9KqZFHA.3048@.TK2MSFTNGP14.phx.gbl...
> You cannot remove this property... you must add another column, move the
> id
> info to the new column, drop the old column, and rename the new column...
> Or do the same thing at the table level... However,
> If you go into SEM, select the table , right click and go into design, you
> can simply select the column and uncheck Identity at the bottom... SEM
> will
> generate a script to do this work for you - if there are lots of rows it
> may
> take a while.
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "Shailaindra Sharma" <shailaindra@.abosoftware.com> wrote in message
> news:eIHMP%23pZFHA.3840@.tk2msftngp13.phx.gbl...
>> Folks,
>> I am trying to alter my database thru SQL Scripts. I have a few tables
> where
>> I have columns having Identity Property. I now want to modify these
> columns
>> having no Identity Property. But I can't find SQL Statement that can do
>> this.
>> Can some one help?
>> Regards
>> Shailaindra Sharma
>>
>|||Consider proposing this:
sqlwish@.microsoft.com
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John" <John@.nospam.infovis.co.uk> wrote in message news:%23iv4mYrZFHA.2664@.TK2MSFTNGP15.phx.gbl...
> Why can't we do things like that which we can easily do in access. How hard
> can it be for ms to add these things which will make life so easy.
> Regards
>|||You mean no one has found it to be a problem as yet? :)
Regards
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:esZQgZsZFHA.3596@.tk2msftngp13.phx.gbl...
> Consider proposing this:
> sqlwish@.microsoft.com
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "John" <John@.nospam.infovis.co.uk> wrote in message
> news:%23iv4mYrZFHA.2664@.TK2MSFTNGP15.phx.gbl...
>> Why can't we do things like that which we can easily do in access. How
>> hard can it be for ms to add these things which will make life so easy.
>> Regards

Removing Identity Property

Folks,
I am trying to alter my database thru SQL Scripts. I have a few tables where
I have columns having Identity Property. I now want to modify these columns
having no Identity Property. But I can't find SQL Statement that can do
this.
Can some one help?
Regards
Shailaindra Sharma
You cannot remove this property... you must add another column, move the id
info to the new column, drop the old column, and rename the new column...
Or do the same thing at the table level... However,
If you go into SEM, select the table , right click and go into design, you
can simply select the column and uncheck Identity at the bottom... SEM will
generate a script to do this work for you - if there are lots of rows it may
take a while.
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Shailaindra Sharma" <shailaindra@.abosoftware.com> wrote in message
news:eIHMP%23pZFHA.3840@.tk2msftngp13.phx.gbl...
> Folks,
> I am trying to alter my database thru SQL Scripts. I have a few tables
where
> I have columns having Identity Property. I now want to modify these
columns
> having no Identity Property. But I can't find SQL Statement that can do
> this.
> Can some one help?
> Regards
> Shailaindra Sharma
>
|||Why can't we do things like that which we can easily do in access. How hard
can it be for ms to add these things which will make life so easy.
Regards
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:ewlf9KqZFHA.3048@.TK2MSFTNGP14.phx.gbl...
> You cannot remove this property... you must add another column, move the
> id
> info to the new column, drop the old column, and rename the new column...
> Or do the same thing at the table level... However,
> If you go into SEM, select the table , right click and go into design, you
> can simply select the column and uncheck Identity at the bottom... SEM
> will
> generate a script to do this work for you - if there are lots of rows it
> may
> take a while.
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "Shailaindra Sharma" <shailaindra@.abosoftware.com> wrote in message
> news:eIHMP%23pZFHA.3840@.tk2msftngp13.phx.gbl...
> where
> columns
>
|||Consider proposing this:
sqlwish@.microsoft.com
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John" <John@.nospam.infovis.co.uk> wrote in message news:%23iv4mYrZFHA.2664@.TK2MSFTNGP15.phx.gbl...
> Why can't we do things like that which we can easily do in access. How hard
> can it be for ms to add these things which will make life so easy.
> Regards
>
|||You mean no one has found it to be a problem as yet?
Regards
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:esZQgZsZFHA.3596@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Consider proposing this:
> sqlwish@.microsoft.com
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "John" <John@.nospam.infovis.co.uk> wrote in message
> news:%23iv4mYrZFHA.2664@.TK2MSFTNGP15.phx.gbl...

Removing Identity Property

Folks,
I am trying to alter my database thru SQL Scripts. I have a few tables where
I have columns having Identity Property. I now want to modify these columns
having no Identity Property. But I can't find SQL Statement that can do
this.
Can some one help?
Regards
Shailaindra SharmaYou cannot remove this property... you must add another column, move the id
info to the new column, drop the old column, and rename the new column...
Or do the same thing at the table level... However,
If you go into SEM, select the table , right click and go into design, you
can simply select the column and uncheck Identity at the bottom... SEM will
generate a script to do this work for you - if there are lots of rows it may
take a while.
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Shailaindra Sharma" <shailaindra@.abosoftware.com> wrote in message
news:eIHMP%23pZFHA.3840@.tk2msftngp13.phx.gbl...
> Folks,
> I am trying to alter my database thru SQL Scripts. I have a few tables
where
> I have columns having Identity Property. I now want to modify these
columns
> having no Identity Property. But I can't find SQL Statement that can do
> this.
> Can some one help?
> Regards
> Shailaindra Sharma
>|||Why can't we do things like that which we can easily do in access. How hard
can it be for ms to add these things which will make life so easy.
Regards
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:ewlf9KqZFHA.3048@.TK2MSFTNGP14.phx.gbl...
> You cannot remove this property... you must add another column, move the
> id
> info to the new column, drop the old column, and rename the new column...
> Or do the same thing at the table level... However,
> If you go into SEM, select the table , right click and go into design, you
> can simply select the column and uncheck Identity at the bottom... SEM
> will
> generate a script to do this work for you - if there are lots of rows it
> may
> take a while.
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "Shailaindra Sharma" <shailaindra@.abosoftware.com> wrote in message
> news:eIHMP%23pZFHA.3840@.tk2msftngp13.phx.gbl...
> where
> columns
>|||Consider proposing this:
sqlwish@.microsoft.com
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John" <John@.nospam.infovis.co.uk> wrote in message news:%23iv4mYrZFHA.2664@.TK2MSFTNGP15.phx
.gbl...
> Why can't we do things like that which we can easily do in access. How har
d
> can it be for ms to add these things which will make life so easy.
> Regards
>|||You mean no one has found it to be a problem as yet?
Regards
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:esZQgZsZFHA.3596@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Consider proposing this:
> sqlwish@.microsoft.com
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "John" <John@.nospam.infovis.co.uk> wrote in message
> news:%23iv4mYrZFHA.2664@.TK2MSFTNGP15.phx.gbl...

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.'?
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'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|||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_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|||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 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.'?
> Hari
>
>

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...
>
>

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.??
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@.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_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_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
|||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 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.??
> Hari
>
>

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
>
>