Drop identity using transact sql

• BanOcto

  Say Hey Kid

  Points: 680

  More actions

  October 24, 2006 at 9:41 pm

  #170248

  Can the identity be dropped from a column using Transact SQL?  Alter table alter column does not appear to provide this option.

  Regards

  BanOcto

• ijaz

  SSCommitted

  Points: 1912

  More actions

  October 25, 2006 at 2:35 am

  #667787

  Hi,

  This need to create a new table changing the identity column to your requirement and migrate the data from the old one.

  Here is sample script for this.

  CREATE TABLE dbo.Table1

   (

   id int identity(1,1) NOT NULL,

   name char(10) NULL

  &nbsp  ON [PRIMARY]

  GO

  BEGIN TRANSACTION

  CREATE TABLE dbo.Tmp_Table1

   (

   id int NOT NULL,

   name char(10) NULL

  &nbsp  ON [PRIMARY]

  GO

  IF EXISTS(SELECT * FROM dbo.Table1)

    EXEC('INSERT INTO dbo.Tmp_Table1 (id, name)

    SELECT id, name FROM dbo.Table1 TABLOCKX')

  GO

  DROP TABLE dbo.Table1

  GO

  EXECUTE sp_rename N'dbo.Tmp_Table1', N'Table1', 'OBJECT'

  GO

  COMMIT

  I hope this will slove your problem.

  Thanks

  Ijaz

  cheers

• BanOcto

  Say Hey Kid

  Points: 680

  More actions

  October 25, 2006 at 5:42 am

  #667810

  Thanks Ijaz

• noeld

  SSC Guru

  Points: 96590

  More actions

  October 25, 2006 at 12:31 pm

  #667966

  becareful!

  if you have triggers or indexes those will not carry over with the above script. If you don't then consider yourself a happy camper

  

  
  * Noel