January 30, 2025 at 6:57 pm
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
January 30, 2025 at 7:08 pm
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".
January 30, 2025 at 10:00 pm
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
Change is inevitable... Change for the better is not.
January 30, 2025 at 10:08 pm
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".
January 30, 2025 at 11:36 pm
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.
January 30, 2025 at 11:57 pm
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
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy