Refactoring T-SQL using SSDT - Part 2

First, my apologies for the delay in posting. Life sometimes has a way of getting in the way of the best intentions. Rest assured it was not due to a lack of desire to do so, just a lack of time.

In this, the second part of my short series on Refactoring T-SQL in SSDT, we're going to go over fully qualifying object names and moving objects to a new schema. These two items are pretty closely related. For instance, perhaps you wish to move all of the objects for a particular application to its own schema. If you previously had not been fully qualifying your object names, you'll need to now.

3. Fully Qualify Object Names

When developing database code, if all your objects reside in the dbo schema, it is pretty easy to fall into the habit of not qualifying your object names. After all, the code works without them. There are good reasons to do so, though. For me the biggest reason is that it simply improves readability. But there are other technical reasons as well. For instance, at compile time, all the objects referenced in the code are resolved by a lookup. If you have not specified the schema, the query optimizer will first look in the users own schema or whatever their default schema is. That extra lookup, while small and only occurring at compile time, is still an extra lookup that could be avoided. Also, specifying the schema now will make changing that schema later, should it be necessary, much easier.

SSDT makes all of this easy. We can refactor a specific object or an entire project, depending on where we right-click. For some reason, however, we're not able to refactor all objects of a specific type (i.e. Stored Procedures) by right-clicking that folder in the solution explorer. For this example, let's refactor a single stored procedure. We first right click on the procedure in the Solution Explorer and select Refactor > Fully-qualify Names...

This will bring up the Preview Changes window just like we saw previously with the Expand Wildcard and Rename refactor tasks. Let's have a look at the kind of things that will be refactored here.

Note that it added the schema to the name of the stored procedure itself as well as to all table names within the stored procedure. It also added table aliases to items in the select list. Had the procedure called any user-defined functions, those would have been qualified as well. Clicking Apply will make the previewed changes to the stored procedure. It's that easy. One interesting thing to note is that references to Common Table Expressions will be fully qualified as well. For instance, if we refactor the MERGE statement used in the post-deployment script that loads the State table, we see that all the references to the CTE for the target of the merge, appropriately called 'Target', are now fully qualified.

4. Move an Object to a Different Schema

SSDT makes this operation a breeze. Previously, you'd have to change the schema of the object and then hunt down every reference to that object and change the schema in all those references. Now it can be done in a single operation. In this case, though, invoking the refactor dialog is a bit tricky. You can't just right-click the object in the Solution Explorer, you have to have the item open and then right click on the object's definition. For instance, let's move the State table to a new schema called 'Lookup'. We need to open the definition for the State table and then right click on the CREATE TABLE statement.

This will bring up the 'Move to Schema' dialog. I'll select 'Lookup' for the New Schema, make sure 'Preview Changes' is checked and click OK.

We're presented with the Preview Changes dialog and we can see that not only is it changing the table definition, but all the constraints on it as well as referencing procedures, functions and pre/post deployment scripts. How cool is this?!

Potential Gotchas

As mentioned above, just where you'll find the refactor menu item you're looking for can be difficult. Many items can be refactored only at the individual item or entire project level, and nothing in-between. Also, some items like the Rename (from Part 1) and Move to Schema refactor presented here are only accessible when right clicking on the object definition, not the object's file in the Solution Explorer.