July 10, 2007 at 3:35 pm
Goal: In a single T-SQL script, search the syntax of all stored procedures in a database for a certain string, delete that string, and then run Alter Procedure for those sprocs that contained the string.
Background: I have restored a database from a live server onto a different server for testing purposes. The database contains sprocs that utilize linked-servers. I don't want the sprocs on the test database to utilize those linked-servers so I need to modify all the sprocs that contain references to the linked server. (The sprocs actually refer to a nested sproc which utilizes the linked server). The way I am doing it now is to: 1) script out ALL the sprocs via Enterprise Manager, 2) search for the string and delete/modify it, 3) for those sprocs that contained the string I change CREATE PROCEDURE to ALTER PROCEDURE, 4) for those sprocs that did not contain the string I delete the sprocs from the script, and then 5) run that batch. Is there an easier way?
July 10, 2007 at 4:16 pm
Minimise calls to linked server. Everywhere you need remote data call one of local SP's (very limited number of them) which access remote server.
Place all calls to linked server in dynamic SQL.
Make Linked Server Name to be retrieved from a table.
Once you've done it you may forget about it forever.
_____________
Code for TallyGenerator
July 10, 2007 at 5:28 pm
Sergiy has a good way down the line... in the interim if you need to find all procedures containing a certain string:
select * from information_Schema.routines where routine_definition like '%linkedsrvname%'
July 10, 2007 at 5:44 pm
If procedure length exceeds 4000 characters and linked server name is after 4000th character this won't work.
_____________
Code for TallyGenerator
July 10, 2007 at 11:59 pm
I had a similar situation a year back. I just wrote a script to use sp_helptext and inserted the text to a hash table and did the manipulation there. A single procedure written by me helped us to move the almost 20 databases within a weeks time which the management expected will take atleast 45 days. I don't have the procedure now but can rewrite it if you want.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply