March 30, 2007 at 4:33 am
Hi, anyone know if its possible to search&replace text inside all the storedprocs of a database , with an other storedproc?
Thanks
March 30, 2007 at 7:12 am
Personally I would export all the sps text to a single text file. Do my search and replaces there. Run the alter scripts in QA environement. Then once all typos are corrected, move to production.
I think this is the safest, yet fast way to do this.
March 30, 2007 at 10:02 am
If you're asking as to whether you can change the text of a stored procedure without having to reset permissions, then "alter procedure" will do that. If you want to search and replace text in several procedures at one time (for example if you have changed a table name), then I don't think it can safely be done. You can get all the text from every stored procedure of interest by using this SQL.
select object_name(id), text from syscomments where text like '%<your text>%' and id in (select id from sysobjects where type = 'P')
Carlos
April 1, 2007 at 11:28 pm
SQL Server 2005 has some improved ways to search the text of a stored procedure, addressing the SQL Server issue of the difficulty in finding search strings that are split between multiple pages (a possibility when stored procs are longer than ~8000 bytes). All three of the below queries will find all instances of a given string in a procedure definition.
SELECT OBJECT_NAME(object_id), Definition
FROM sys.sql_modules
WHERE Definition LIKE '%proctext%'
AND OBJECTPROPERTY(object_id, 'IsProcedure') = 1
SELECT Name, OBJECT_DEFINITION(object_id)
FROM sys.procedures
WHERE OBJECT_DEFINITION(object_id) LIKE '%proctext%'
SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%proctext%'
AND ROUTINE_TYPE = 'PROCEDURE'
cheers, Malcolm
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply