how to drop identitiy on a column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

how to drop identitiy on a column

Author

Topic

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-09-17 : 01:59:32

iam having table EMRCommonMedicationsLkup where EZEMRX_MED_ID is having the identity property.because of this i accnot perform data maipulations.now i want to drop the identity.the column is having the primary key too.what will be the solution in doing.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-17 : 02:13:59

Remove the identity property via SSMS

Madhivanan

Failing to plan is Planning to fail

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-09-17 : 02:18:15

how can i do that using sql server management studio express.please show me example or else give mes ome script

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-17 : 02:44:15

Right click on table-->Design
You can see the properties at the bottom of the window
Remove indentity from there and save

Madhivanan

Failing to plan is Planning to fail

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-09-17 : 03:04:28

ANY SCRIPT HELP BECAUSE NEEDS TO RUN ON PLENT OF DATABASES SO WHAT.PLEASE

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-09-17 : 03:31:15

Thanks i got it

ALTER TABLE EMRCommonMedicationsLkup DROP CONSTRAINT EMRCOMMONMEDICATIONSLKUP_PK
GO
ALTER TABLE EMRCommonMedicationsLkup add EZEMRX_MED_ID_bak NUMERIC(13,0)
GO
UPDATE EMRCommonMedicationsLkup set EZEMRX_MED_ID_bak = EZEMRX_MED_ID
GO
ALTER TABLE EMRCommonMedicationsLkup ALTER COLUMN EZEMRX_MED_ID_bak NUMERIC(13,0) NOT NULL
GO
ALTER TABLE EMRCommonMedicationsLkup drop column EZEMRX_MED_ID
GO
EXEC sp_rename 'EMRCommonMedicationsLkup.[EZEMRX_MED_ID_bak]', 'EZEMRX_MED_ID', 'COLUMN'
GO
ALTER TABLE [dbo].[EMRCommonMedicationsLkup] ADD
CONSTRAINT [EMRCOMMONMEDICATIONSLKUP_PK] PRIMARY KEY CLUSTERED
(
[EZEMRX_MED_ID]
) ON [PRIMARY] ---------------------------
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources