January 22, 2007 at 5:06 pm
Is there a way to script modifications of stored procedures to essentially find\replace text within them database-wide?
We are maintaining sql servers for our flagship application, and often require "refreshes" of one environment from another. For example, our Test environment periodically requires to be refreshed using a backup of the current production database.
Now, several of the stored procedures include explicitly-defined database names. In production, several of the stored procedures may point to "prod_database_1" for example. When we restore this database to our test environment, those sprocs are no longer valid, because prod_database_1 doesn't exist in this environment. Rather, they need to be re-created to point to "test_database_1" instead.
Manually, this process is fairly simple: Script out the given stored procedure as a drop/create SQL statement, do a find/replace on the name in question, and run the SQL back in. Simple, sure, but certainly not efficient when there are 50+ stored procedures to do this against. The database includes several thousand sprocs, so scripting them all and manipulating that script is not the best bet either. Also, the list of sprocs that require this activity may change, and we want to make sure we don't miss a "new" sproc that requires "localization".
So, the question is: How to systematically find\replace text within stored procedures to recompile them with the correct information for the given environment?
January 22, 2007 at 7:45 pm
why is the database name hardcoded in the stored procedure in the first place ?
January 23, 2007 at 8:49 am
In our testing envrionments, we have more than one "system" on a given instance. A "system" is comprised of up to 5 databases, and the sprocs in question reference a database within the system other than their own. The hardcoded entries are not for the database that they currently reside in.
January 23, 2007 at 11:34 am
Are the references to other databases to tables and views in other databases, or to stored procedures in other databases?
If they only reference tables and views in other databases, you can solve this by creating views in the current database that reference the tables in the other databases, and changing your stored procedrure to reference only the views in the local database. Then write a stored procedure that creates those views that accepts database names as a parameters. When you setup a new environment, all you will have to do is run the procedure that creates the views one time.
January 25, 2007 at 12:15 am
Test systems should at least try to mimic production.
If the database is called 'prod_database' in production, then call it 'prod_database' in test, dev, qa, whatever. Name the server or instances something different, but object names should not be fiddled with - the script that built the objects in dev must work in test, qa, and all the way through to production. Otherwise, you aren't testing the scripts that will be applied to production. You are testing the scripts that get modified on the way to production.
Leave the names alone, and your problems from three-part naming go away. Pay no heed to the unfortunate decision to place 'test' and 'prod' in the database names.
-Eddie
Eddie Wuerch
MCM: SQL
January 25, 2007 at 9:17 am
So, to answer my original question...
I've created a script that takes any list of sprocs, triggers or views based on any given criteria, makes any find\replace modifications to the code, scripts them out as alter statements, and re-applies them to the database.
I'm dumping each to a temporary table using sp_helptext, stripping out the cr/lf characters, modifying the CREATE to ALTER, and then bcp'ing that table to an <owner>-<objectname>.sql file. Osql then executes the file against the db, and we're good to go.
Thanks for your suggestions.
-A
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply