Alter table alter column

• joemai

  Hall of Fame

  Points: 3496

  More actions

  December 29, 2005 at 4:45 pm

  #188764

  Hi All,

  I want to alter my table to change a column to int identity from sql, but it did not allow me to do so:

  alter table  ad_requests

  alter column ad_request_id int [identity] (1,1)

  It kept giving me error: 'Incorrect syntax near 'identity'.

  Is there any trick to do that? I know I can change it from EM, but I'd like to do it in my script.

  Thanks,

  Minh Vu

• TriggerMe

  SSChampion

  Points: 11752

  More actions

  December 29, 2005 at 4:56 pm

  #612073

  Unfortunately you can't use ALTER TABLE ALTER COLUMN to change IDENTITY Column Properties using T-SQL.

  You will have to do it via EM.

  ---

  Kindest Regards,

• David.Poole

  SSC Guru

  Points: 75656

  More actions

  December 30, 2005 at 1:48 am

  #612092

  If you look at the way that EM does it.

  CREATE TABLE dbo.tmp_yourtablename ( the new field list )

  IF EXISTS(SELECT * FROM dbo.tmp_yourtablename)

  EXEC('INSERT INTO dbo.dbo.tmp_yourtablename (the new field list)

  SELECT the new field list FROM dbo.a_Test (HOLDLOCK TABLOCKX)')

  GO

  DROP TABLE dbo.yourtablename

  GO

  EXECUTE sp_rename N'dbo.tmp_yourtablename', N'yourtablename', 'OBJECT'

  GO

  LinkedIn Profile