In my previous post I gave an overview of SSDT, SQL Server Data Tools.In this post I will give some hints on refactoring of the data structures.
One of the common refactoring actions is a column rename. Let’s take a look at that. Open a table in design view by double-clicking on it solution explorer. Your view should look similar to the following.
There is no obvious menu that supports renaming. Just click on a column in the T-SQL Pane, right click on it, then choose Refactor sub-menu, then Rename menu item.
Next screen will allow you to change the name of the column
Now just publish to local database, and you are set to go. So, how does this magic work? You will see a file with your project name and extension refactorlog in your project now. Just open it, and you will see how renaming is handled in database projects.
<?xml version="1.0" encoding="utf-8"?> <Operations Version="1.0" xmlns="http://schemas.microsoft.com/sqlserver/dac/Serialization/2012/02"> <Operation Name="Rename Refactor" Key="4bdbbf1f-6154-4129-9e42-3b904570cd7c" ChangeDateTime="03/17/2012 20:10:47"> <Property Name="ElementName" Value="[dbo].[Assignment].[Assigned]" /> <Property Name="ElementType" Value="SqlSimpleColumn" /> <Property Name="ParentElementName" Value="[dbo].[Assignment]" /> <Property Name="ParentElementType" Value="SqlTable" /> <Property Name="NewName" Value="[AssignedDate]" /> </Operation> </Operations>
The same procedure applies when you rename the table. Just right click on a table name in T-SQL pane.
Pretty easy, right?
If you take a closer look at the refactoring menu, you will see other useful actions, especially if you are working with stored procedures. For example, let me add a stored procedure that select all columns from Assignment table above.
I am going to add a folder, called procedures to my project, then add new stored procedure item using Add New Item menu.
I am going to start as following.
CREATE PROCEDURE [dbo].[SelectAllAssignments] AS SELECT * From Assignment RETURN 0
Then, I right click on * symbol, select Refactor, then Expand Wildcards
Now, just click Apply.
You can also choose option to Fully Qualify Names:
The last option is to move objects to a different schema. To accomplish that, I first create new schema called users.
CREATE SCHEMA [users]
Of course, this is also an item in my project. Then I can edit my stored procedure and select move to schema refactoring
My new schema was available from drop down already. My refactorlog file was updated accordingly. Now publish to test the changes.
I hope this gives you a taste of refactoring functionality in SSDT.