How to update multiple column definition in single update statement

  • Hi All,

    Does anybody know if there is a way to update column definition of multiple columns in a single ALTER statement in SQL Server 2008 or Denali CTP 1 ?

    I am aware it is not possible in SQL Server 2005.

    Thank You

  • Can you elaborate in better

    can you have some examples for it like how with scenarios

    Thanks

    Parthi

    Thanks
    Parthi

  • I don't think you can do it in SQL 2008 R2 either. Not sure about Denali.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • I need to alter definition of two columns in a table and its being done like this :

    ALTER TABLE dbo.JURISDICTION_FIELD

    ALTER COLUMN FIELD_CODE NVARCHAR(60) NOT NULL;

    ALTER TABLE dbo.JURISDICTION_FIELD

    ALTER COLUMN [DESCRIPTION] NVARCHAR(2000) NULL;

    If I need to update 20 columns in a table, there will need to be 20 ALTER TABLE statements. Is there a way to update all 20 columns using a single ALTER TABLE statement?

    Please let me know if you need more clarifications.

    Thank You.

  • I don't believe there is a way to do that. You can add multiple columns in a single command, but not alter multiple columns all at once.

    Copy-and-paste can remove most of the work with that. Might be even simpler if you're modifying a predictable set of columns, and you query sys.columns to build the basic strings.

    select 'alter table [' + object_name(object_id) + '] alter column [' + name + ']'

    from sys.columns

    where object_id = ...;

    Something like that.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply