Alter Table

  • Hi,

    I want to alter a table to add a particular column at specified ordinal position ..

    Ex..

    Suppose i have table structure like ...

    COL1        COL2         COL4

    ------      ------        ------

    A             B               C

    F              I               J

    Now i want to add a column at third position like..

    col1         col2       col3      col4

    -----         -----    -----      ----

    can it be possible using alter statement in sql server 2000 ?

    Thanks...

    Note : I can be possible through Sql server Enterprise manager.. but i want this using query..

     

     

  • yes - something like

    sp_configure 'allow updates',1

    go

    reconfigure with override

    go

    alter table mytable add column col3 char(1) NULL

    go

    update syscolumns set colorder=5 where name='col4' and id=object_id('mytable')

    update syscolumns set colorder=3 where name='col3' and id=object_id('mytable')

    update syscolumns set colorder=4 where name='col4' and id=object_id('mytable')

    go

    sp_configure 'allow updates',0

    go

    reconfigure with override

    MVDBA

  • You can do the alter through EM, then hit the script button and get the script. Then don't save your changes and you'll have the script.

  • Or, if you are not confident with directly modifying the system tables, you could do the following

    alter table mytable add col3 char(1) NULL, col4_copy char(1) NULL

    Update mytable set col4_copy = col4

    Alter Table mytable drop column col4

    EXEC sp_rename 'Mytable.Col4_copy', 'Col4', 'COLUMN';

     

    A bit long winded perhaps.....

     

     

  • Hi ,

    Thanks for all your reply..

     

    Thanks a lot..

    Regards.

    Amit Gupta...

     

  • The ordinal position cannot matter. If you want to see columns in a specific order, select them in that order.

  • the ordinal position CAN matter. Not everyone writes good SQL or applications and you can often fid yourself with "Select *" or "insert into x select *" in which case the ordinal position does matter...

    we know it's not good practice, but it still happens and often you're lumped with application code that you can't change...

    MVDBA

  • Steve,

    have you tried scripting out "Enterprise mangler" code? great for small tables, but it has a tendency to be backwards compatable and Drop the table and recreate it....

    MVDBA

  • Create a new table... populate the data and rename the tables...

    EM also using the same method...

    It simple syscolumns.colorder column update but I don't advise you to update the system tables...

     

    MohammedU
    Microsoft SQL Server MVP

  • Michael,

    In that case, it's not the ordinal position that breaks things, it's the poor code.

  • as i said in my post - it's bad practice - we wouldn't do it, but the forums are here to help not to preach about what developers SHOULD be doing that we do normally.

    a DBA's job often involves "getting things to work" where we have no control over the design (take for example anyone running sage - who specify a Binary master database collation because they're still rooted in sql 7 technology!)

    MVDBA

  • So if someone will ask you to help to commit suicide and blow up some people around will you give him a hand?

    If some guy will need a cover while robbing a shop would you consider "to help not to preach about what he SHOULD be doing"?

    _____________
    Code for TallyGenerator

  • Michael,

    You're too funny!!! You say we're not here to preach, yet you're preaching at me!!!!

    I just read your solution to the original question: update syscolumns????? IMHO, that's preaching suicide.

    Back when I was learning - the had way - NOT to update system tables, I managed to toast a couple of servers.

    You didn't even bother to say that this is

    a) HIGHLY not recommended;

    b) not going to work very much longer.

    No sir, we're ALL better off teaching them how to fish, rather than giving them today's dinner.

Viewing 13 posts - 1 through 12 (of 12 total)

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