Post reply

use T-SQL to change table column schema

  • December 15, 2003 at 5:43 am

    #101807

    Hi,

    Is there any way to use T-SQl to change the column ORDER of a table.

    eg

    tblWronglyOrdered

    column3

    colum1

    column2

    I'd want the columns to be:

    colum1

    column2

    column3..

    I know that I can drop and then recreate the table with the correct columnOrder..but can T-SQL do the same?

    cheers,

    yogiberr

  • December 15, 2003 at 5:47 am

    #486143

    Why???

    Frank

    http://www.insidesql.de

    http://www.familienzirkus.de

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • December 15, 2003 at 6:28 am

    #486147

    good question.

    some of the columns in the table are now less important than they were when I created them.so, when i go to QA, i'd like to return all the columns, but I'd like to see the columns that are most relevant first.

    yogi,

  • December 15, 2003 at 6:45 am

    #486148

    AFAIK there is no way using T-SQL.

    This might be interesting http://qa.sqlservercentral.com/forum/link.asp?TOPIC_ID=12631

    Frank

    http://www.insidesql.de

    http://www.familienzirkus.de

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • December 15, 2003 at 7:01 am

    #486151

    righto.

    Thanks Frank.

    yogi

  • December 15, 2003 at 7:52 am

    #486152

    If the order of the columns meant anything you could create a view and use that instead of the base table. Then you could re-arrange the view columns any time you would like.

    Dr. Peter Venkman: Generally you don't see that kind of behavior in a major appliance.

    Patrick

    Quand on parle du loup, on en voit la queue

  • December 15, 2003 at 8:01 am

    #486168

    never thought of that.

    nice one.

    yogi

  • December 16, 2003 at 8:16 am

    #486411

    Yes, there's a way!! Go into Enterprise manager, open the table in design view, move your column to where you want it be.

    Then click the "Save Change Script" icon on the toolbar. This will script out the change you just made. It's a lot of code, and basically, the table is being dropped and recreated and then the rows are inserted back into the table.

    Hope it helps!

    Bryan

  • December 16, 2003 at 8:29 am

    #486415

    Good to know.

    This way you can schedule it for off-peak hours.

    Frank

    http://www.insidesql.de

    http://www.familienzirkus.de

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • December 16, 2003 at 11:42 am

    #486475

    quote:

    Yes, there's a way!! Go into Enterprise manager, open the table in design view, move your column to where you want it be.

    Then click the "Save Change Script" icon on the toolbar. This will script out the change you just made. It's a lot of code, and basically, the table is being dropped and recreated and then the rows are inserted back into the table.

    Hope it helps!

    Bryan

    Good answer, but the poster specifically said:

    I know that I can drop and then recreate the table with the correct columnOrder..but can T-SQL do the same?

    yogiberr,

    Do you still need the data in the 'old' columns? If not, just drop those columns. If you do need them, I think pbirch has the right idea.

    -SQLBill

  • December 16, 2003 at 12:43 pm

    #486492

    Hi Bill,

    as it happens, I don't need the data that is in the old columns..still, it's good to get as many options as were suggested.

    thanks all.

    yogi.

Viewing 11 posts - 1 through 10 (of 10 total)

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