Alter Identity Property of a column

• skraj

  SSC Rookie

  Points: 27

  More actions

  July 10, 2002 at 11:04 am

  #79059

  Hi,

  I created table with an Identity Column say ID. In an Alter Table T-SQL how do i turn off this Identity Property for this column i.e i don't need this column to be identity anymore. I know that this can be done using Enterprise Manager but i want to do it through Alter Table statement. Could any one help me.

  e.g

  Create Table dbo.IdentityTable(ID int Identity(1,1), Descp varchar(2))

  Go

  Insert IdentityTable(Descp) Values('SQ')

  Insert IdentityTable(Descp) Values('LQ')

  Insert IdentityTable(Descp) Values('MQ')

  go

  Alter Table IdentityTable Alter Column ID int not null

  go

  What is the statement to take OFF the Identity Property

  thanks,

  Sundar

• K. Brian Kelley

  SSC Guru

  Points: 114703

  More actions

  July 10, 2002 at 11:35 am

  #432371

  It can't be done through an ALTER TABLE statement currently.

  Enterprise Manager creates a new table without the IDENTITY property, moves data into it, drops the old table, then renames it.

  This is about the only way to do it.

  K. Brian Kelley

  bkelley@sqlservercentral.com

  http://www.sqlservercentral.com/columnists/bkelley/

  K. Brian Kelley
  @kbriankelley