SQL Server Performance

alter multiple column

Discussion in 'General Developer Questions' started by sabesh, Aug 2, 2006.

  1. sabesh New Member


    Hello there, is it possible to alter multiple columns with one alter table command?

    i.e. (this works fine)
    alter table dbo.table_name
    alter column dateOpened varchar(50)

    but I would like to alter more than one columns:

    i.e. (something like)
    alter table dbo.table_name
    alter column dateOpen varchar (50), dateClosed varchar (50)

    When I tried this, getting a syntax error.

    Any help would be appreciated.

    Thanks,
    Sabesh
  2. LearnSqlServer.com New Member

    AFAIK you must write a separate statement for each. I haven't dug down into the docs but the comma between the columns is what gives you a syntax error.

    /*******************************

    Scott Whigham

    Check outhttp://www.LearnSqlServer.com/VideoTutorials/ - SQL Server 2005 and 2000 Tutorials

    *******************************/
  3. thomas New Member

    I don't think so. You can use a single ALTER TABLE statement to add multiple columns, but not to alter them.
  4. FrankKalis Moderator

    You can't do that. Here's the syntax from BOL.

    quote:
    ALTER TABLE table
    { [ ALTER COLUMN column_name
    { new_data_type [ ( precision [ , scale ] ) ]
    [ COLLATE < collation_name > ]
    [ NULL | NOT NULL ]
    | {ADD | DROP } ROWGUIDCOL }
    ]
    It only mentions one column at a time.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
    Heute schon gebloggt?http://www.insidesql.de/blogs
  5. sabesh New Member

    Thank you all for the quick response.<br />This will satisfy myself for writing each command to alter each column<img src='/community/emoticons/emotion-5.gif' alt=';)' /><br /><br />thanks again

Share This Page