Refactoring T-SQL using SSDT - Part 1

One of my favorite tools in my bag of SQL tricks is SQL Server Data Tools (SSDT) for Visual Studio. It's the successor to the old "DB Pro's" projects and can be a T-SQL junkie's best friend. I just recently presented on SSDT at the Feb FoxPASS meeting and I've submitted a similar talk to SQL Saturday #206 in Madison, WI on April 6th. Over the next few weeks, I'll be posting about some of my favorite features in SSDT as well as some of my biggest complaints and work-arounds. This is not an introduction to SSDT. For that I'd refer you any of a number of great SSDT resource on the web, including Gert Draper's great talk at TechEd.

Today I want to talk about Refactoring T-SQL code in SSDT. SSDT has a number of refactor options, I'll be going over two of them today and others later in the week.

1. Expand a Wildcard

We all recognize that SELECT * will only end in tears. SSDT, however, provides a quick and easy way to eliminate its use from a project and expand those wildcards into a nice list of columns. You can do so at both the project level or for just a single instance. To refactor a single instance, simply right click on the offending line and select Refactor > Expand Wildcards...

You're then given the opportunity to preview the changes that will be made. You can choose to apply or Cancel.

If you click Apply, you'll see that we now have expanded the wildcard to select all the columns of the table by name.

You can also expand all the wildcards in the entire project or solution by right-clicking on the project in the Solution Explorer.

It will search for all wildcards in the entire project or solution and you'll see all of them in the Preview Changes dialog.

It really is that simple! When I inherit a new application or am first given a database to deploy from a developer, one of the first things I do is use these refactor methods to root out some bad practices. SSDT also give us the ability to do static code analysis, but that's a topic for another post.

2. Rename a Column or Table

A task that should be fairly straightforward is to rename a table or column. Whether it's in order to bring a project in line with corporate naming standards or just because you changed your mind, renaming an object can often be an exercise in frustration. Finding all instances of the referenced object can involved querying sys.syscomments, sys.sysdepends and other tables and even then you may not find ALL of the references. SSDT does a great job of tracking dependencies and can make this job a snap. Simply go to the definition of the object you wish to rename, right click on it and select Refactor > Rename...

You are then presented with a dialog where you can enter the new name for the object. In this case, I'm renaming the "Birthday" column to "DOB".

When you click OK, much like with the Expand Wildcard refactor, you'll be presented with a Preview Changes dialog where you can see every object that SSDT tracked down that will need to change and the text of the change it will be making. In this case you can see that we're not only changing the table definition, but it found a stored procedure that refers to the column we're changing and has renamed the reference for us. Incidentally, it's the same stored procedure where we expanded the wildcard earlier. The most common opposition to expanding wildcards I've encountered is that it makes renaming columns difficult, but the Rename Refactor eliminates this excuse. Handy!.

After you click apply, the previewed changes will be made and you're all set. Or are you? Notice in the Solution Explorer a new file has appeared at the root of the project called "Sample_SSDT_refactorlog". The filename will be different depending on the name of the project (Sample_SSDT in this case), but you will have a new file here. What the heck is it?

Let's have a look. If we inspect the file, we'll see that it is an XML file that is a log of all the refactor commands that change the data structure of the project. Our Expand Wildcard is NOT in here, but the Column Rename IS.

Why is that? What is this file used for? It confused me as well, and since it had "Log" in the name, I just removed it as unnecessary cruft in the project. Then I went to deploy my changed project to my Dev-Integration server and the data that was in the renamed column disappeared. Whoops. It seems that SSDT and the Data Tier Application Framework (DACFX) use this file to know when a data-containing object is renamed, otherwise it would believe that the old object was dropped and an unrelated new object was created. In our case, DACFX will actually copy the data from the old column to the new if we leave this file in place. When a rename operation is done at Deploy-time, a new system table named __RefactorLog will appear in the database. It contains the Operation Key of the rename operation that has been applied and corresponds to the same Key in the log file in the project. This is so DACFX can keep track of what rename operations have occurred already and can apply any new ones that may conflict if not applied in the correct order. This is all pretty flipping cool, if you ask me.

Potential Gotchas

The sad reality, though, is that SSDT is not perfect. It can do a lot, but it can't do everything. One big thing you'll need to be aware of when doing these two refactor operations is that SSDT cannot refactor Dynamic SQL. In our sample project there is a stored procedure that uses dynamic SQL to build a WHERE clause. You'll see that it also uses a wildcard that did not get expanded. 

Dynamic SQL, to SQL Server and to SSDT is just a string. A string that is later passed to sp_executesql, but a string none-the-less. As such, SSDT can't be expected to be able to refactor it. This is a case where you'll have to be careful, and know your application and whether it uses dynamic SQL so you can refactor those bits by hand.