Replace the specific text in store procedures
set nocount on
if OBJECT_ID('tempdb..#dbs') is not null
drop table #dbs
if OBJECT_ID('tempdb..#objects') is not null
drop table #objects
declare @find as nvarchar(128) = 'Monkey'
declare @replace as nvarchar(128) = 'Chimp'
declare @SQL as nvarchar(max)
declare @current_db as sysname
declare @current_schema as sysname
declare @current_object as sysname
declare @current_type as char(2)
declare @current_ansi as bit
declare @current_quot as bit
declare @fullname as sysname
declare @preamble as nvarchar(128)
create table #objects
(
dbname sysname,
schemaname sysname,
objname sysname,
objtype char(2),
ansinulls bit,
quotedidentifier bit
)
create unique clustered index i on #objects (dbname, schemaname, objname)
select [name] into #dbs
from master.sys.databases
where [name] not in ('master','tempdb','model','msdb','ReportServer','ReportServerTempDB', 'SSISDB')
declare db_cursor cursor for select [name] from #dbs order by [name]
open db_cursor
fetch next from db_cursor into @current_db
while @@FETCH_STATUS = 0
begin
set @SQL = 'insert into #objects select ''' + @current_db + ''', s.[name], o.[name], o.[type], m.uses_ansi_nulls, m.uses_quoted_identifier from ' + @current_db + '.sys.sql_modules as m '
+ 'join ' + @current_db + '.sys.objects AS o ON m.object_id = o.object_id '
+ 'join ' + @current_db + '.sys.schemas AS s ON o.schema_id = s.schema_id '
+ 'where m.definition like ''%' + @find + '%'' and type in (''P'', ''V'') and is_ms_shipped = 0 order by s.[name], o.[name]'
exec sp_executeSQL @SQL
fetch next from db_cursor into @current_db
end
close db_cursor
deallocate db_cursor
declare obj_cursor cursor for select dbname, schemaname, objname, objtype, ansinulls, quotedidentifier from #objects order by dbname, objname
open obj_cursor
fetch next from obj_cursor into @current_db, @current_schema, @current_object, @current_type, @current_ansi, @current_quot
while @@FETCH_STATUS = 0
begin
set @fullname = @current_db + '.' + @current_schema + '.' + @current_object
set @preamble = CASE WHEN @current_ansi = 1 THEN 'SET ANSI_NULLS ON' ELSE 'SET ANSI_NULLS OFF' END + '; '
+ CASE WHEN @current_quot = 1 THEN 'SET QUOTED_IDENTIFIER ON' ELSE 'SET QUOTED_IDENTIFIER OFF' END + '; '
print 'Altering ' + @fullname
if @current_type = 'P'
begin
set @SQL = 'use ' + @current_db + '; ' + @preamble + 'declare @newproc nvarchar(max);'
+ 'set @newproc = REPLACE(REPLACE(OBJECT_DEFINITION(OBJECT_ID(''' + @fullname + ''')), ''' + @find + ''', ''' + @replace + '''), ''CREATE PROCEDURE'', ''ALTER PROCEDURE''); '
+ 'exec sp_executeSQL @newproc'
exec sp_executeSQL @SQL
end
if @current_type = 'V'
begin
set @SQL = 'use ' + @current_db + '; ' + @preamble + 'declare @newproc nvarchar(max);'
+ 'set @newproc = REPLACE(REPLACE(OBJECT_DEFINITION(OBJECT_ID(''' + @fullname + ''')), ''' + @find + ''', ''' + @replace + '''), ''CREATE VIEW'', ''ALTER VIEW''); '
+ 'exec sp_executeSQL @newproc'
exec sp_executeSQL @SQL
end
fetch next from obj_cursor into @current_db, @current_schema, @current_object, @current_type, @current_ansi, @current_quot
end
close obj_cursor
deallocate obj_cursor