Anexinet BI Blog: Renaming objects with SSDT

This will be the first of a few posts covering topics from my SQL Saturday presentation on June 1^st of 2013. If you have not checked out SSDT since they were called Database Projects, it is well worth another look. Besides an improved interface and other new features, it just works seamlessly.

With database projects, one would generate a deployment script that might be 20 or 30 thousand lines of code. Besides, hardly being able to effectively review it, it was always white knuckles hoping that it would not error out 15,000 lines or so into it. Some object changes were tough to trust that they would work right and, occasionally, some tinkering was required to make the scripts work. With SSDT, database refactoring can now largely be an automated process, which makes it possible to have continuous integration on your data warehouse projects. All of this shortens up the development cycle and reduces the deployment effort.

Refactoring (renaming objects or changing schemas) was part of database projects as far as deployments were concerned, but now it is integrated into schema comparisons in SSDT. I will walk through the basics of below.

The Problem

Renaming an object or changing schemas is often a time consuming and difficult task depending upon how many references there are to it. For example, suppose we have a column with a typo, “Customer_Numbr” (not that I have ever done that before). Not only, do we need to rename the column, but this column might be part of a PK, index, foreign key, stored procedure, view or extended property. All of these references need to be discovered and updated as well.

The Solution

SSDT makes this simple. You can access the refactor menu by right-clicking on an object in SSOX (SQL Server Object Explorer) – this would be a table, view, column or function, for example. Or you can right click on an object within a script. In our case, we would click on the column name and then Go to Refactor> Rename. Note that you can also change the schema from here as well.

Once we select “rename” the dialogue will give the current name, prompting you to change it. In the case of this example, we would make our new name “Customer_Number.” By default, “Preview Changes” is checked and so, when we hit “ok,” get the following window which will allow us to see all of the scripts that reference the object and what will be changed in this action. This takes away all the tedium of finding all the references that require a change in reference and, really, it’s a lifesaver because it is easy to overlook some type of objects like extended properties. If the column is referenced in a proc in another database in the project which has a reference to it, this script will be changed as well.

When we click “Apply” all of the files mentioned in the menu are changed and saved. More than this, SSDT will keep track of these changes and how they are applied so that when it comes time for deployment, no data loss will occur. For example, if we changed the name of a column, when SSDT compares the new name to the old, it would have no sure way of knowing that this was a rename and it would simply rebuild the table, dropping the old column and fitting in the new one.

With refactoring, the change is saved so that when a comparison is done, the renaming can be applied. There are two places which store this information – first, the refactorlog and second, a system table in the target database called dbo.__refactorlog. The refactorlog file will be added to the root of the project when the first refactoring change is made. The file will be name <DatabaseName>.refactorlog and the contents will look something like this:

<?xml version="1.0" encoding="utf-8"?>

As you can see, the old and new names are tracked in a generic xml format, allowing it to be reused against any object that can be renamed or have the schema transferred.

When you publish the change, the rename operation will be put into the script unless the column does not exist. As well, it will create the __refactorlog table and add the entry with the guid from the project, noting that the change has been applied.

-- Refactoring step to update target server with deployed transaction logs

IF OBJECT_ID(N'dbo.__RefactorLog') IS NULL

CREATE TABLE [dbo].[__RefactorLog] (OperationKey UNIQUEIDENTIFIER NOT NULL PRIMARY KEY)

EXEC sp_addextendedproperty N'microsoft_database_tools_support', N'refactoring log', N'schema', N'dbo', N'table', N'__RefactorLog'

IF NOT EXISTS (SELECT OperationKey FROM [dbo].[__RefactorLog] WHERE OperationKey = '2ed039e3-c6ba-4985-9abf-d82a33c9baad')

INSERT INTO [dbo].[__RefactorLog] (OperationKey) values ('2ed039e3-c6ba-4985-9abf-d82a33c9baad')

Note that the table will appear under the system tables in SSMS. The beauty of this design is that a refactoring change can be applied to any target database in any state and take the correct action given the condition it is in.