• SQL Prompt
• SQL Refactoring

Refactoring Databases with SQL Prompt

Many of the tools that SQL Prompt provides you are ones that you’ll use more or less every single day you write T-SQL code. The refactoring tools in SQL Prompt are more like the ones in the snakebite kit that you take on a long hike in the desert. You hope not to have to use them too often, but when you do, they are extremely valuable. My previous article focused on small scale code refactoring, limited to altering the ‘internals’ of a single batch or object. A less frequent, but harder, requirement is to change the ‘public interface’ of an object, such as by changing the name of an object, or column, or even by splitting tables to achieve a better design.

Smart Rename

Changing the name of a code object, table or column can be a laborious, even daunting task, because of all the dependencies that can exist in your database. Throughout all the code and constraints, you must make sure you know about all the possible side-effects of one seemingly-simple change. Making these changes by hand may, reasonably, only take a few hours, but who has a few hours?

SQL Server has tools to help you discover dependencies, like the sys.sql_expression_dependencies catalog view (which I made use of in this blog post to find three- and four-part names), or you can use the object dependency viewer in SSMS, simply by right-clicking an object and choosing View Dependencies, although the UI is a bit lean on details.

Alternatively, Redgate’s SQL Dependency Tracker tool integrates with SSMS and provides a detailed dependency diagram for any selected object. For example, in the SSMS Object Explorer, right-click on Purchasing.PurchaseOrders, in the WideWorldImporters database, and choose “View Dependency Diagram for [object]…“. Figure 1 shows the many objects that reference it.

Figure 1: Dependency diagram for Purchasing.PurchaseOrders

This diagram hints at the magnitude of the task ahead of you, if you need to make name changes manually. Thankfully, we can use SQL Prompt’s Smart Rename feature, which will automatically modify almost all references to the renamed object, in the current database. Dynamic SQL references will not be handled, so this feature does not obviate the need for solid test plans.

We’ll start with the simplest database refactoring task, renaming a code module, then work our way up the ladder of complexity and risk, renaming tables and then finally columns.

Renaming code objects

Let’s say you write a new stored procedure, Purchasing.PurchaseOrder$ListFinalized that calls an existing stored procedure, Purchasing.PurchaseOrder$List, to get a result set consisting of finalized orders only.

Listing 1

Now, you decide you need to change the name of the existing Purchasing.PurchaseOrder$List procedure to PurchaseOrder$ListAll, to clarify that it will return all purchase orders, regardless of whether they are finalized.

Having located the stored procedure in SSMS object explorer, you can rename it by hitting F2, or right-clicking and selecting Rename, but all this will do is rename the object, so any existing code that still references this object by its old name will now fail.

Msg 2812, Level 16, State 62, Procedure Purchasing.PurchaseOrder$ListFinalized, Line 4
  Could not find stored procedure 'Purchasing.PurchaseOrder$List'.

Instead, we will use SQL Prompt’s Smart Rename feature. Right click on Purchasing.PurchaseOrder$List in Object Explorer and choose Smart Rename. Change the name in the dialog to PurchaseOrder$ListAll, as shown in Figure 2.

Figure 2: Using Smart Rename to rename a stored procedure

Click Next and you’ll see the list of tasks SQL Prompt will perform to rename the object and adjust any dependent objects that reference it by name.

• Drop procedure [Purchasing].[PurchaseOrder$List]
• Create procedure [Purchasing].[PurchaseOrder$ListAll]
• Alter procedure [Purchasing].[PurchaseOrder$ListFinalized]

Execute the script that was generated, and SQL Prompt will make the changes. If there are errors, the script will fail, and it will roll back all changes.

Renaming Tables

While changing the name of a coded module will generally be painless, changing table and column names require more care, and you’ll want to check the generated script carefully, so you know exactly what it is doing. Sometimes the process fails to modify certain objects due to the features they use in SQL Server, and you’ll need to intervene and modify the generated script, manually.

Simple table renaming

Let’s say that for some strange reason we want to rename the Purchasing.PurchaseOrders table to Purchasing.ThePurchaseOrders. Right-click on the table and choose Smart Rename. Change the name to ThePurchaseOrders and click Next. SQL Prompt lists out all the necessary actions, to account for all the dependencies (as visualized in Figure 1).

Figure 3: Using Smart Rename to rename a table

Click View Script to see the script that it will execute, which includes altering our stored procedure, Purchasing.PurchaseOrder$ListAll to reference the new table name.

Listing 2

Execute the script, and you will see a set of PRINT statements informing you of each of the changes that it made.

Limitations of Smart Rename

For most tables, Smart Rename is practically magic, but it does have a few limitations that we need to demonstrate. Luckily, WideWorldImporters provides us with several tables that are a doozy to change, such as Application.Cities, which has schema-bound access, temporal extensions, and row-level security, all of which we will need to handle manually.

Let’s say we want to give the Application.Cities table a new name. Again, simply right-click on the table and choose Smart Rename. Now, however, you will see a much longer list of actions, due to the dependent objects that reference the object we propose to change.

Figure 4: Non-trivial table rename

If you try to execute the script, it will fail. The first error is caused by the attempt to rename Cities to TheCities and the error is as follows. The generated script uses IF @@ERROR <> 0 SET NOEXEC ON and so subsequent steps do not run, causing further superfluous errors, not shown here.

Msg 15336, Level 16, State 1, Procedure sp_rename, Line 565 
  Object '[Application].[Cities]' cannot be renamed because the object participates in enforced dependencies.

This illustrates a limitation of the Smart Rename feature. The generated script simply uses a call to the sp_rename stored procedure, but this will not work for every table. Here, for example, it is not a supported operation on temporal tables, such as Application.Cities, so it will not work.

To avoid this error, you will need to recode this block of code to ALTER the Application.Cities table to turn off system versioning, change the name of the table (and probably its associated history table, Application.Cities_Archive(History), for the sake of clarity), then re-enable system versioning.

However, in this case, there are further complications. The WideWorldImporters database implements row-level security, which is implemented using security policies. One of these policies, FilterCustomersBySalesTerritoryRole contains predicates that reference an inline table-valued function (iTVF) called Application.DetermineCustomerAccess which references the Application.Cities table. This iTVF uses schema-binding, meaning we cannot alter or drop it while it is still being referenced (by the security policies), but we need to alter it because it references the Application.Cities table that we want to rename.

As you can see, situations like this can lead to a big wave of required, manual changes. We’ll need to alter to the security policy, to drop the predicates that reference the iTVF, so that we can then drop the iTVF, so that we can disable system versioning, so that we can then rename the table. Once we’re done, we’ll then need to re-enable system versioning, recreate the iTVF and re-establish the working security policy.

Listing 3

Clearly this was a non-trivial task, but all the object changes we handled for you, other than the schema bound function, system versioning, and the row level security. Most of these changes will not be the norm for the tables you encounter, but you’ll need to deal with each of these situations occasionally.

Tip: Along with a backup of your database, which you should always have when making structural changes like renaming an object, it is a good idea to use another SQL Toolbelt tool: SQL Compare. Use it to capture a snapshot of the code in your database before making any changes, and then compare the database to the snapshot, after your changes have been completed. This will let you look for any changes you did not expect, without having to use a backup. For example, if you have dropped a schema-bound object, you may have lost security on that object. It is also very comforting to see that nothing has changed after a failed deployment, because you didn’t realize you had to deal with row-level security first!

Nevertheless, renaming a table is a relatively safe task for your code’s public interface. The table name will not generally be in the output of your queries, so if all your access if via stored procedures or views, it’s a safe change to make. However, renaming a column is a completely different story.

Renaming columns

Imagine that two weeks into a project, you’ve written a bunch of T-SQL coded objects, views, triggers, procedure, constraints, and so on, and then suddenly realize that that the Product table has a column that is misspelled as ProductNmber. You need to change this before releasing it. I’ve lost count of the number of times I’ve finished building a set of tables or new columns only to then realize I’ve misspelled “hybid”, or “soliciation”. Of course, as much as I love SQL Prompt’s code completion, it will auto-fill “hybid” just as easily as “hybrid”, so you may not notice the mistake until code review time

As an example, we will make a change to the OrderDate column in our newly-renamed ThePurchaseOrders table. Our Purchasing.PurchaseOrder$ListAll stored procedure returns the PurchaseUserID, the OrderDate and the IsOrderFinalized columns. In other words, these three columns are part of the interface.

Listing 4

If we want to rename one of those columns of the table, we can again use Smart Rename. Just like for the table example, right-click on the OrderDate column in the SSMS object explorer and rename it to OrderDate2. SQL Prompt finds all the objects that reference this column, including the Purchasing.PurchaseOrder$ListAll procedure, and the resulting script updated it accordingly.

Listing 5

However, this means users of this procedure will now see OrderDate2, instead of OrderDate. If this is a new development and no-one has started using the code yet, then it’s not really a problem, but if you need the users’ view to stay the same, you will need to fix the code. This sort of problem would have been averted easily if the original query had used aliases, as shown in Listing 6, since now the any subsequent change to the column name would not affect that public interface.

Listing 6

The real concern is that, unless you are religiously using aliases, you may end up with a mix of places where the interface changed, and places where it has not, for a column change. Since you will be presented with the actual script that will be used to change the column, you can very easily use Find on the script to determine what to change.

Splitting Tables

You won’t often need to, or want to risk the disruption of, splitting an existing table into two, but when you do then SQL Prompt’s Split Table feature could save you a lot of time and pain and effort. The main reason I have considered doing this is to ‘segregate’ the very large columns in an existing table, often for performance reasons, but sometimes just for convenience.

As an example, say we wanted to add system versioning to the Purchasing.ThePurchaseOrders table. We only want to keep version history on the OrderDate2 column. In reality, even if we wanted to version most of the table, we may not want to keep history on the two nvarchar(max) columns, since every update would create a new copy of up to 2GB of text.

Hence, our goal will be to move the OrderDate2 column out of ThePurchaseOrders table and into a new table, to which we can then apply system versioning. Right-click the ThePurchaseOrders table, choose Split Table and the wizard will appear. Give the new, secondary table a name, like ThePurchaseOrdersTemporal, and on the next screen, you will copy the key, PurchaseOrderId, and move the OrderDate2 to the new table as seen in Figure 5. Both tables will have primary keys, so you cannot have duplicate rows in either table.

Figure 5: Splitting a table

The next screen will ask you to create a foreign key, which establishes the order in which you will need to insert data into these tables.

Figure 6: Creating a FOREIGN KEY

This ensures that any row added to the Purchasing.ThePurchaseOrders table references an existing value in the PurchaseOrderID column of Purchasing.ThePurchaseOrdersTemporal.

Clicking Next, you will see the information about actions that will be taken, dependencies for your change, and any warnings that pertain to the required actions. In this case, we see warnings that it can’t handle non-standard filegroups, nor guarantee to automatically preserve data when dropping the column from the parent table (although, in this case, the generated script will save your data in the new table).

Figure 7: Warnings for the table rename script

Of course, you will always expect to have to check and fine-tune one of these generated scripts. SQL Prompt may not get it right every time. You’ll want to be sure that the changes that the tool has made fits the way you plan to work with the data. For example, the OrderDate2 column was defined as NOT NULL. But, now that the column is in a related table, it is technically nullable, because you cannot enforce a 1-1 relationship.

Clicking the View Script button will cause the tool to generate a script that you can use to apply the changes. It will create the new table and its primary key, load the data into the newly created table, drop the column from the original table, alter all dependent objects, add the FOREIGN KEY and finally establish extended properties on the columns of the new table.

All dependent objects will be altered to account for the new schema design. For example, the PurchaseOrder$ListAll procedure was modified to replace the reference to Purchasing.ThePurchaseOrders and with an INNER JOIN between Purchasing.ThePurchaseOrders and Purchasing.ThePurchaseOrdersTemporal, as you can see in Listing 7.

It is an INNER JOIN because it is expected that both rows are necessary, since they would be a part of the table together. This is true even if you had chosen only columns that allow NULL values (something you may not wish to be true, so review each case independently and change the code accordingly.)

Listing 7

As with any process where tables are modified and possible data losses may occur, I strongly suggest you review the generated scripts, and test the deployment at least once on a copy of your database structures and have a backup available if something in the script is wrong. Once you’ve fully satisfied, you can run the script and then apply temporal extensions to the Purchasing.ThePurchaseOrdersTemporal table, and not the original table.

One last reminder to test your code and make sure all the code works as you expect it to, both in your SQL Server objects and your user interfaces. You are changing the object’s interface to the outside world greatly.

Summary

In this article, we have looked at two of the least-used features in SQL Prompt, which are nevertheless invaluable when you need them. If you have to rename an object, or column, or even split a table into two tables, there can be no doubt that the Smart Rename and Split Table features can save you a great deal of time, especially if you have implemented your SQL Server database using a server-centric paradigm, heavy with constraints, triggers, and stored procedures.

You can make large scale name and structure changes with much less effort, meaning you can devote more time and effort to testing that your applications continue to function exactly as expected, post-refactoring.

Tools in this post

SQL Prompt

Write, format, and refactor SQL effortlessly

Find out more

SQL Toolbelt

The industry-standard tools for SQL Server development and deployment.

Find out more