Mass Renaming of Tables and Stored Procedures

Question

I need to rename all of my tables, stored procedures and obviously the code within each stored procedure that was referencing the old table names.

Why is the best way to do this?

Some methods I have considered:

SP_Rename - Gets half the job done. However this doesn't change the code within the SP itself

In addition to RedGates' Refactor, I found this set of tools here http://www.easysqltools.com/EasySQLSmartRename.aspx which does the exact same thing (I get the feeling their version is based on Refactor because the UI looks almost exactly the same) which runs in SSMS Express which happens to be the version I run. 15 day trial should be more than enough for most purposes.

Answer 1

SQLRefactor from RedGate has a smart rename feature. It updates all the references to the renamed objects!

See here:http://www.red-gate.com/products/SQL_Refactor/features.htm

Answer 2

I would use sp_rename for the tables.

For everything else, I would script the database, do a search and replace on the text file, and then execute the script back to your database:

In Management Studio, right click on the database name, then 'Tasks', then 'Generate Scripts'.

Don't script all objects in the selected database, just stored procedures, synonyms, user defined functions and views.

Also, make sure you set the following options: Script Drop, Script Object Level Permissions, Script Create.

I realize this is a little tedious, and it depends how many objects we're talking about, and (of course), I would do this is in development first, but I've used this process before and it works well.

Answer 3

There's a book on this and related subjects:

Refactoring Databases: Evolutionary Database Design

There are tricks that can help, and I think it's worth doing - but it ain't easy. Good luck!