How should I rename many Stored Procedures without breaking stuff?

Question

I've inherited an ASP.NET website with C# behind, pulling in data from MS SQL Server.

The database has had several successive maintainers over the years and any naming guidelines that may have once been in place have been ignored.

I'd like to rename the stored procedures to a consistent format. Obviously I can rename them from within SQL Server Managament Studio, but this will not then update the calls made in the website code behind.

Is there anything I can do to ensure all calls get updated to the new names, short of searching for every single old procedure name in the code? NB I do not believe my Q to be a duplicate of this question as the latter is solely about renaming within the database.

Answer 1

Short of testing every path in your application to ensure that any calls to the database and the relevant stored procedures have been updated... no.

Use global search and replace (but review each suggested replacement) to try to avoid missing any instances. If you app is well structured then there really should only be 1 place each stored proc is called.

Answer 2

If you use a connection to DB, stored procedures etc, you should create a service class to delegate these methods.

This way when something in your database, SP etc changes, you only have to update your service class, and everything is protected from breaking.

There are tools for VS that can manage changing a name, like refactor, and resharper

Answer 3

You will need to handle this in at least two areas, the application and the database. There could be other areas as well, and you have to be careful not to overlook them.

The Application

A Nice Practice for Future Projects

It helps to abstract your sprocs out. In our apps, we wrap all of our sprocs in a giant class, I can make calls like this:

Dim SomeData as DataTable = Sprocs.sproc_GetSomeData(5)

That way, the code end is nice and encapsulated. I can go into Sprocs.sproc_GetSomeData and tweak the sproc name in just one place, and of course I can right click on the method and do a symbolic rename to fix the method call solution-wide.

Without the Abstraction

Without that abstraction, you can just do Find In Files (Cntl+Shift+F) for the sproc name and then if the results looks right, open the files up and Find/Replace all the occurances.

The Sql Server

Don't Trust View Dependencies

On the SQL server end, theoretically in MSSMS 2008 you can right click on a sproc and select View Dependencies. That should show you a list of all the places where the sproc is used in the database, however my confidence in this feature is very low. It might be better in SQL 2008, but in previous versions it definitely had problems.

View Dependencies hurt me, and it will take time for that to heal. :)

Wrap It!

You end up having to keep the old sproc around for awhile. This is the major reason why renaming sprocs is a such a project - it can take a month to finally be done with it.

First replace its contents with some simple TSQL that calls the the new sproc with the same parameters, and write some logging so that once some time goes by, you can tell if the old sproc is actually unused.

Finally, when you're sure the old sproc is unused, delete it.

Other Areas?

There could be a lot of other areas as well. Reporting Services springs to mind. SSIS packages. Using the technique of keeping the old sproc around and re-routing to the new one (mentioned above) will help you know if you missed anything, however it won't tell you what you missed. This can lead to much pain!

Good luck!

Answer 4

You could make the change in stages:

1. Copy of the stored procedures to the new stored procedures under their new name.
2. Alter the old stored procedures to call the new ones.
3. Add logging to the old stored procedures when you've changed all the code in the website.
4. After a while when you're not seeing any calls to the old stored procedures and you're happy you've found all the calls in the web site, you can remove the old stored procedures and logging.

Answer 5

As far as changing your application, I have all my stored procs as settings in the web.config file, so all the names are in one place and can be changed at any time to match changes to the database.

When the application needs to call a stored proc, the name is determined from web.config.

This makes it easier to manage all the potential calls which the application could make to the database services layer.

Answer 6

I did this and I relied heavily on global search in my source code for stored procedure names and SQL digger to find sql procs that called sql proces.

http://www.sqldigger.com/

SQL Server (as of SQL 2000) poorly understands it own dependencies, so one is left searching the text of the scripts to find dependencies, which could be other stored procs or substrings of dynamic sql.

Answer 7

You can move the 'guts' of the SPROC to a new SPROC meeting your new naming conventions, and then leave the original sproc as a shell / wrapper which delegates to the new SPROC. You can also add an 'audit' table to track when the old wrapper SPROC is called - this way you will know that there are no dependencies on the old SPROC, and the old SPROC can be safely dropped (also, make sure that it isn't just 'your app' using the DB - e.g. cross database joins or other apps)

This has a small performance penalty, and won't really buy you that much (other than being able to 'find' your new SPROCs easier)

Answer 8

It will be a bit of a tedious search through your source code and other database objects I'm afraid.

Don't forget SSIS Packages, SQL Agent Jobs, Reporting Services rdl as well as your main application code.

You could use a regular expression like spProc1|spProc2 to search in the source code for all object names at the same time if you have a tool that supports searching through files using regular expressions (I have used RegexBuddy for this in the past)

If you want to just cover the possibility you might have missed the odd one you could leave all the previous stored procedures behind for a month and just have them log a custom SQL trace event with APP_NAME(), SUSER_NAME() and any other info you find helpful then have it call the renamed version. Then set up a trace monitoring this event.

Answer 9

Use a utility like FileSeek to search the contents inside each and every file in your project folder. Don't trust the windows search - it's slow and user-unfriendly.

So if you had a Stored Procedure named OldSprocOne and want to rename it to SP_NewONe, search all occurrences Of OldSprocOne then search all occurrences of OldSprocOne to see if that name isn't already being used somewhere else and won't cause problems. Then rename each and every occurrence in the code.

This can be very time consuming and repetitive for larger systems.

Answer 10

I would be more concerned about ignoring the names of the procedures and replacing your legacy DAL with Enterprise Library Data Access Block 5

Database Accessors in Enterprise Library 5 DAAB - Database.ExecuteSprocAccessor

Having code that is like

 public Contact FetchById(int id)
 {
  return _database.ExecuteSprocAccessor<Contact>
   ("FetchContactById", id).SingleOrDefault();
 }

Will have atleast a billion times more value than having stored procs with consistent names, especially if the current code passes around DataTables or DataSets ::shudders::

Answer 11

1. I would obtain a list of references to a procedure by using the following, because SSMS dependencies doesn't pickup dynamic SQL references or references outside the database.

   SELECT OBJECT_NAME(m.object_id), m.*
     FROM SYS.SQL_MODULES m
    WHERE m.definition LIKE N'%my_sproc_name%'
   

   • The SQL needs to be run in every database where there could be references.
   • syscomments and INFORMATION_SCHEMA.routines have nvarchar(4000) columns. So if "mySprocName" is used at position 3998, it won't be found. syscomments does have multiple lines but ROUTINES truncates. Should you disagree, take it up with gbn.
2. Based on that list of dependencies, I'd create new stored procedures starting the foundation stored procedures - those with the least dependencies. But I'd mind not to create stored procedures, prefixing the name with "sp_"
3. Verify the foundation procedures work identically to existing ones
4. Move to the next level of stored procedures - repeat steps 1-3 as needed till the highest level procedure has been processed.
5. Test the switch over the application uses to the new procedure - don't wait until the all the procedures are updated to test interaction with the application code. This doesn't need to be done for every stored procedure, but waiting to do this wholesale isn't a great approach either.

Developing in parallel has it's risks too:

• Any changes to existing code needs to also be applied to the new code. If possible, work in areas where development is frozen or use a bug fix as an opportunity to migrate to new code rather than apply the patch in two places (while also minimizing downtime for transition).

Answer 12

I'me all in favor of refactoring any sort of code.

What you really need here is a method slowly and incrementally renaming your stored procs.
I certainly would not do a global find and replace.

Rather, as you identify small pieces of functionality and understand the relationships between the procs, you can re-factor in small pieces.

Fundamental to this process, though, is source-code control of your database.
If you do not manage changes to your database the same as normal code, you will be in serious trouble.

Have a look at DBSourceTools. http://dbsourcetools.codeplex.com
It's specifically designed to help developers get their databases under source code control.

You need a repeatable method of restoring your database to a specific state - prior to refactoring.
Then re-apply your refactored changes in a controlled way.

Once you have embraced this mindset, this mammoth and error-prone task will become simple.

Answer 13

This is assuming that you use SQL Server 2005 or above. An option that I have used before is to rename the old database object and create a SQL Server Synonym with the old name. This will allow for you to update your objects to whatever convention you choose and replace the refrences in code, SSIS packages, etc... as you come along them. Then you can concentrate updating the references in your code gradually over however maintenance releases you choose (as opposed to breaking them all at once). As you feel that you've found all references you can remove the synonym as the code goes to QA.