'exec sp_executeSQL sp_executeSQL next from obj_cursor into obj_cursor 'exec sp_executeSQL sp_executeSQL = 'V' Set = 'use ' ' ' 'declare nvarchar(max) ' CASE WHEN = 1 THEN 'SET QUOTED_IDENTIFIER ON' ELSE 'SET QUOTED_IDENTIFIER OFF' END ' ' Set = '.' '.' = CASE WHEN = 1 THEN 'SET ANSI_NULLS ON' ELSE 'SET ANSI_NULLS OFF' END ' ' 'where m.definition like ''%' '%'' and type in (''P'', ''V'') and is_ms_shipped = 0 order by s., o.'Įxec sp_executeSQL next from db_cursor into db_cursorĭeclare obj_cursor cursor for select dbname, schemaname, objname, objtype, ansinulls, quotedidentifier from #objects order by dbname, objnameįetch next from obj_cursor into = 0 'join ' '.sys.schemas AS s ON o.schema_id = s.schema_id ' 'join ' '.sys.objects AS o ON m.object_id = o.object_id ' Set = 'insert into #objects select ''' ''', s., o., o., m.uses_ansi_nulls, m.uses_quoted_identifier from ' '.sys.sql_modules as m ' Where not in ('master','tempdb','model','msdb','ReportServer','ReportServerTempDB', 'SSISDB')ĭeclare db_cursor cursor for select from #dbs order by 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 Comments
Leave a Reply. |