Smart Rename
While this is not a particularly common thing, sometimes it is necessary to rename objects, columns, object owners or schemas, and parameters. This can become very messy if what you want to rename is used in a lot in pre-existing code or dependent objects. SQL Refactor has the ability to allow you to rename an object, column, object owner or schemas, or parameters, and for that renamed item to be automatically propagated throughout the database, potentially saving a lot of time.
To show you how this works, I decided to change the name of a table in a large database application. My goal was to change dbo.ap_delfm_test to dbo_ap_delfm_tbl. When you begin this process you see the following screen, where you specify what you want to change.
Next, SQL Refactor analyzed the database (in less than eight seconds in my example) and produced the following results screen.
The first tab of this screen (above) shows you what needs to be done (and in what order) to change the name of the table, and then to propagate the change throughout the entire database. As you can see, there were lots of things that needed to be changed to account for the change of the table name.
The Warnings tab displays any potential problems it finds. In my case, it did not find any.
The Referencing Objects tab displays a list of the objects that need to be altered to work correctly with the newly renamed table. This is informational only.
Assuming you are happy with what you see in the three tabs, you can select View Script, which displays a script that is used to make all the necessary changes so that your entire database is updated to know that the table name was changed. This particular script was 1,171 lines long. Below you can see a very small part of it.
Once you run this script, the item will be renamed and any other changes needed to propagate the change throughout the database will be made.
I tried this many times, using objects that had few references throughout the database (not requiring a lot of changes) and using objects that had many, many references throughout the database (requiring many changes). In almost every case, the process was a success. In a couple of very complex change requests, the process seemed to work at first, creating the necessary change script, but when I ran the script, the script failed at one or more points, preventing the change from being properly propagated throughout the entire database. Because of the potential for very complex changes to fail, be sure you back up your database before you run the change script, and ideally, test it on a test database before you run it on a production database. Note that in the case of script failure, SQL Refactor will automatically roll back the changes it makes to the database.