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.
Enjoy.
Thanks.
Hello, While refactor rename of Column, table is not refactoring the Primary constraint. Is there any problem. Let say example table product i renamed to ProductsInfo and Column Name is Info to ProductInfoId, it is not renaming my primary key column constraint like PX_product.
Although it would be nice, but PK is a separate object, so you would need to rename it separately. I do not think refactor menu supports constraints, but you can easily do so in the table sql file
Hi, how about changing a data type or extending a decimal precision? This refactor menu only works on RENAME, not changing any data type.
@Gray. You do not need refactor those use cases, just change the types in the table script window or designer window.
I can’t for the life of me get SSDT to generate the right SQL script to handle a data type change. I am changing a data type from datetime to varchar(10). It recognizes the change but does not handle renaming the table and loading the data at all, it assumes a simple alter column will handle it but it doesn’t. I need to make SSDT generate a table rename script but can’t figure out how. And ideas would be much appreciated.
Does alter column generate an error when script is run? You may have to create a custom script and run it in pre-deployment. You can do anything in the custom script.
When you update data type of column directly in table script without any refactoring, hmm… all related procedures with variables linked to this column are not refactored. So, this causes crash of your application at most cases.
Yes that is why you have to use ssdt as recommended.