Special thanks go to David Cherry, a long-time friend, who provided the drawings.
#Notepad ++ sort by dewey manual
You should have received a copy of the GNU General Public License along with this manual if not, write to the Free Software Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA See the GNU General Public License for more details. This manual is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. It also handles idiosyncratic ANSI_NULL and QUOTED_IDENTIFIER settings, and can be extended to handle the various types of function.īe careful though! With great power comes great responsibility.This manual is free software you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation either version 2 of the License, or (at your option) any later version. + '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.
#Notepad ++ sort by dewey upgrade
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.