Change all occurances of one field in DB

  • Hi everyone

    I am doing some clean up of my DB.  There is one field that I want to change in all tables and SP.   For example, tableA has fieldA.  I want to change the name from fieldA to fieldB in all tables and SP.  I don't want to open each table and SP to do the change.  Is there a query that I can run that will do this for me?

    Thank you

  • Yes.

    EXEC sys.sp_rename 'dbo.table_name.column_name', 'new_column_name', 'COLUMN'

    for each table column that you want to rename.

     

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • You will need to do the rename the column inside of every coded object, as well.  The textual content is located in sys.sql_modules by object ID.  Obviously, you're going to need some dynamic SQL to do that.

     

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

  • You could also likely work around changing the db code using views, if changing the code is just too difficult to do quickly by hand.  Typically that doesn't cause performance issues.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • the best, and probably correct, way is to use your visual studio (or vs code) solution (you do have one don't you!!!) and its schema compare.

    while there are 2 ways of doing it, the one likely simpler to you is to rename the columns within the database, and within the visual solution source do a global replace of said column to new one - this will also change the SP code/views/functions you have.

    then do a schema compare with your database and this should pick up the SP's that have changed and create a deploy script to execute.

  • frederico_fonseca wrote:

    the best, and probably correct, way is to use your visual studio (or vs code) solution (you do have one don't you!!!) and its schema compare.

    while there are 2 ways of doing it, the one likely simpler to you is to rename the columns within the database, and within the visual solution source do a global replace of said column to new one - this will also change the SP code/views/functions you have.

    then do a schema compare with your database and this should pick up the SP's that have changed and create a deploy script to execute.

    Now there's something I didn't know.  Thanks, Frederico!

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

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

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