If OBJECT_ID('tempdb.#objects') is not nullĬreate unique clustered index i on #objects (dbname, schemaname, objname) Ideal for changing hard-coded linked server names and the like: set nocount on Searches all stored procs and views in all user databases on a server, and automatically replaces the search string with another. Here's one I wrote today to help with a server upgrade project. then make sure everything looks good and run UPDATE spText = REPLACE(REPLACE(spText,'CREATE PROCEDURE', 'ALTER spText FROM now copy and paste result into new window also replace CREATE PROCEDURE with ALTER PROCEDURE find and replace search string in stored procedures INSERT INTO add GO after each stored procedure insert stored procedure text into a temporary table ORDER BY 'sp_helptext '''+OBJECT_SCHEMA_NAME(id)+'.'+OBJECT_NAME(id)+''' '
SELECT DISTINCT 'sp_helptext '''+OBJECT_SCHEMA_NAME(id)+'.'+OBJECT_NAME(id)+''' 'įROM syscomments WHERE TEXT LIKE '%' + + '%' ESCAPE '\' I am using custom escape character here since i need to espape in search string get text of all stored procedures that contain search string
set "Result to Text" mode by pressing Ctrl+TĭECLARE VARCHAR(1000), VARCHAR(100), VARCHAR(100) I found this script where you can define search for and replace by text and simply run it to get text replaced in all procedures at once.
0 kommentar(er)
