Post reply

Rename a column

  • December 5, 2007 at 10:08 am

    #181066

    Is there a way to rename a column via script instead of using Management Studios?

    Thanks Art

    Thanks,
    Art
    Database Analyst
    Tastefully Simple, Inc.

    alorenzini@tastefullysimple.com
    " Some days, it's not even worth chewing through the restraints!

  • December 5, 2007 at 10:35 am

    #757394

    yeah, there's a stored procedure for it:

    exec sp_rename 'tablename.columnname','newname','COLUMN'

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • December 5, 2007 at 10:50 am

    #757403

    Thanks. That works.

    Thanks,
    Art
    Database Analyst
    Tastefully Simple, Inc.

    alorenzini@tastefullysimple.com
    " Some days, it's not even worth chewing through the restraints!

  • December 6, 2007 at 12:15 am

    #757539

    Unless you're using it to make a wholesale change to all the tables in a database, renaming columns in a proc is usually a bad idea...

    ... why are you renaming columns?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 6, 2007 at 6:46 am

    #757630

    I am not doing it in a Proc. It is a one time run, in which we are populating a datawarewarehouse from a legacy system.

    Thanks,
    Art
    Database Analyst
    Tastefully Simple, Inc.

    alorenzini@tastefullysimple.com
    " Some days, it's not even worth chewing through the restraints!

  • December 6, 2007 at 6:53 am

    #757637

    alorenzini (12/5/2007)

    Thanks. That works.

    Make sure you refresh all dependent views (see sp_refreshview) if they were using select * (bad practice). Also, if you are not using select $ in these views, do make sure that the dependent views, stored procedures, etc continue to work. sp_rename just renames the column in the table.

    Also, since you were looking at Management Studio, you can check the script that Management Studio would have used. you open the table in the table designer (rightclick on table, Design), rename the columnas, and instead of saving you click on the Generate Change Script button (top left button in the toolbars) (it will show you exactly what Management Studio would have done)

    Regards,

    Andras

    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

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

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