Can a database be refactored in SQL Server?

Question

We have a relatively new database project that we have been writing views and stored procedures for over the last 6 months or so.

• The database works
• The views work
• The stored procedures work

However, the general scope of the project has grown considerably since this project was started, and now some of the table names and field names are a little off base.

For example, the main table in the database is called SheetMetalRequest, but the project has grown to where an actual Sheet Metal Request is now but one of the enumerable types of requests. So, the name on this table is misleading to people coming into the project.

In addition to this main table, there are various columns in other tables that need to be changed as well (think SheetMetalRequestID, for example).

The problem with making this change are the 10 different Views and 30 Stored Procedures that are now a part of this database. Naturally, if something gets changed and not everything is changed with it, our working pre production database could take several man hours to get back to a running state.

It would be GREAT if there were a Refactor ability somehow like what exists in Visual Studio.

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY('productlevel'), SERVERPROPERTY('edition')

productversion | productlevel | edition
10.0.1600.22   | RTM          | Standard Edition (64-bit)

Answer 1

If a tool like SSDT does not work for you for whatever reason, you could do this manually at your own pace. Sometimes a "click one button and go home" approach is actually not what you want.

You could start with introducing synonyms, e.g.

CREATE SYNONYM dbo.Requests FOR dbo.SheetMetalRequest;

Now you can refactor your code in pieces, pointing each reference to the new name. When you are confident that you have captured all references (and this won't be easy even with a tool like SSDT, since it can't see code outside of the database - or even code in dynamic SQL inside the current database), you can drop the synonym and rename the table:

BEGIN TRANSACTION;
    DROP SYNONYM dbo.Requests;
    EXEC sp_rename 'dbo.SheetMetalRequest', 'Requests', 'OBJECT';
COMMIT TRANSACTION

(You'll also want to update all of your modules with sp_refreshsqlmodule in a loop, twice, to make sure you've completely corrected dependencies.)

This would allow you to update different portions of your code (not just views / stored procedures but also middle-tier classes and even front end stuff) to reference the right names, and not have to do it all at once.

Answer 2

Red Gate's SQL Prompt has a 'smart rename' feature, which sounds like is what you are looking for. This will not only rename the object, but also any references to it in other objects. This is a commercial tool from the company I work for, but has a 14-day fully functional evaluation period.

See http://www.red-gate.com/products/sql-development/sql-prompt/features