alter field type

[capik79]

Hi Guys,
I'm using SQL server 2000.

How do I alter column/field from type int (with Identity = Yes Not For
Replication) to just normail int field. No more identity. I want it to be done
using SQL script( sql query analyzer).

Please help me on this, thx

Regards,
Shaffiq

[MikerRoo]

There is no way to directly remove the identity setting.

You must store the column or the table to a temp table and then drop the
original column or table and then rename the copy to the original name.

Note that even when you change the table via Enterprise Manager (the only way
sanctioned by Microsoft); this copy, drop, and rename process is what happens
behind the scenes.

The attached shows how to remove the identity feature from column iID of the
table SOME_TABLE that has only one other column, sFoo.

Be sure to back up your database before trying this!


BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_SOME_TABLE
(
iID int NOT NULL,
sFoo varchar(50) NOT NULL
)
GO

IF EXISTS (SELECT * FROM dbo.SOME_TABLE)
EXEC
(
'INSERT INTO dbo.Tmp_SOME_TABLE (iID, sFoo)
SELECT iID, sFoo
FROM dbo.SOME_TABLE TABLOCKX'
)
GO

DROP TABLE dbo.SOME_TABLE
GO

EXECUTE sp_rename N'dbo.Tmp_SOME_TABLE', N'SOME_TABLE', 'OBJECT'
GO
COMMIT

[capik79]

Hi MikerRoo,
Thx for the solution. No wonder when i run alter command for column its not
working. Even analyzer return success message but changes not successfull.
Table still have an identity fields.
Thanks for the solution.

Regards,
Shaffiq