Post reply

Alter Table

  • December 26, 2006 at 2:24 am

    #170797

    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..

     

     

  • December 26, 2006 at 4:04 am

    #679948

    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

  • December 26, 2006 at 9:41 am

    #679984

    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.

  • December 27, 2006 at 8:15 am

    #680090

    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.....

     

     

  • December 27, 2006 at 8:20 am

    #680093

    Hi ,

    Thanks for all your reply..

     

    Thanks a lot..

    Regards.

    Amit Gupta...

     

  • December 27, 2006 at 8:55 am

    #680103

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

  • December 30, 2006 at 2:27 am

    #680620

    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

  • December 30, 2006 at 2:29 am

    #680621

    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

  • December 30, 2006 at 1:58 pm

    #680629

    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

  • January 2, 2007 at 7:03 am

    #680735

    Michael,

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

  • January 3, 2007 at 3:27 am

    #680913

    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

  • January 3, 2007 at 4:25 am

    #680921

    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

  • January 3, 2007 at 6:41 am

    #680946

    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