Find and REMOVE string from stored procedures

,

This script extends the "Find strings in text of stored procedures" Script by LiviuB and and now finds the
string and removes it.
You may modify script to just comment out the text.
This script removes the text (in this case 'set dateformat dmy') from all non-system stored procedures in a database.
Modify the variable @searchstring to contain the text you want removed.
It does so by modifying the system table : syscomments.
It then marks the stored procedures for recompilation.

This is an extension of the "Find strings in text of stored procedures" Script by LiviuB found at
http://sqlservercentral.com/scripts/contributions/1240.asp
Therefore I would like to acknowledge the basis.

Note: Ensure the stored procedures are fine after running the script.

/*
Description:
This script extends the "Find strings in text of stored procedures" Script by LiviuB and and now finds the
string and removes it. 
You may modify script to just comment out the text but do so at your own risk.
This script removes the text (in this case 'set dateformat dmy') from all non-system stored procedures in a database.
Modify the variable @searchstring to contain the text you want removed.
It does so by modifying the system table : syscomments.
It then marks the stored procedures for recompilation.

This is an extension of the "Find strings in text of stored procedures" Script by LiviuB found at
http://sqlservercentral.com/scripts/contributions/1240.asp
Therefore I would like to acknowledge the basis.

Note: Ensure the stored procedures are fine after running the script.

Patti Mwasi pmwasi@yahoo.com 13-10-2004
*/

set nocount on
declare @colid smallint,@letter int
declare @bintObjectID bigint,@i int
declare @objname varchar(100),@sqlstring varchar(500),@text varchar(8000),@text2 varchar(8000)
declare @searchString varchar(100) --modify this to your needs.

set @searchString = 'set dateformat dmy'
set @sqlstring = '
USE master
EXEC sp_configure ''allow updates'', ''1''
RECONFIGURE WITH OVERRIDE'
exec (@sqlstring)


if object_id('tblObjects') is not null
	drop table tblObjects

create table tblObjects(
  ID bigint identity(1, 1) 
, ObjectID bigint not null
, ObjectName varchar(100) not null
, colid smallint
)

insert into tblObjects(ObjectID, ObjectName,colid )
select C.id, object_name(C.id),c.colid 
from syscomments as C
join sysobjects as O on O.id = C.id
	and o.type = 'P'
where C.text like '%'+ @searchString +'%' -- Modify this statement to suit your needs.

--alter table tblObjects add constraint PK_Objects primary key (ObjectID)

declare curObj cursor 
for
select ObjectID,ObjectName,colid from tblObjects

open curObj

fetch next from curObj into @bintObjectID,@objname,@colid
while @@fetch_status = 0
	begin
		begin
				select @text  = (convert(nvarchar(4000),case when ([status] & 2 = 2) then (uncompress([ctext])) else [ctext] end))
				from syscomments where id = @bintObjectID and colid = @colid

				select @text = (replace (@text,@searchString,''))
				set @i = 0 
				set @text2 = ''
				while @i <= len(@text)
					begin
						select @letter = ASCII(SUBSTRING(@text, @i, 1))
						if   @letter <> 0 
							begin
								select @text2 = @text2 + (SUBSTRING(@text, @i, 1))
							end
						

						set @i = @i + 1
					end

				update syscomments set [ctext] = convert(varbinary(8000),@text2)
				where id = @bintObjectID and colid = @colid
				EXEC sp_recompile @objname
		end

	fetch next from curObj into @bintObjectID,@objname,@colid
	end

close curObj
deallocate curObj

if object_id('tblObjects') is not null
	drop table tblObjects

set @sqlstring = '
USE master
EXEC sp_configure ''allow updates'', ''0''
RECONFIGURE WITH OVERRIDE'
exec (@sqlstring)
GO
print 'Complete.'

Rate

Share

Share

Rate