February 18, 2007 at 8:13 pm
I want to delete all stored procedures that contain a certain line of text. What is blocking me from accomplishing this is that I can't do the following:
IF EXISTS(SELECT Text FROM (EXECUTE sp_helptext procname) WHERE Text = 'string I'm looking for') DROP PROCEDURE....
The EXECUTE can't be put in there, from what I can tell. It looks like I can maybe do something with OPENQUERY, but I haven't figured that out, and it requires changing some DB settings. I know I can do this with a temp table, which is what I'm doing now, but I'm pretty sure this is not optimal for performance reasons. What would you recommend?
SQL Server 2005, Enterprise Edition.
February 18, 2007 at 8:52 pm
A temp table is the best way to go. I presume you are storing the results of the stored proc in a temp table and then using some sort of a cursor to go through the table line by line.
You could, if desired, look at the code of sp_helptext and pick out the relevant bits so you could avoid the temp table.
February 18, 2007 at 9:56 pm
Actually I was just using a SELECT Text FROM #temp WHERE Text = 'my text'
Is the performance hit from the temp table not as bad as I'm fearing?
February 18, 2007 at 11:40 pm
declare @objname Varchar(8000)
select @objname = ''
select @objname = @objname+object_name(id)+',' from syscomments where text like '%my text%'
and objectproperty(id, 'ismsshipped') = 0
select @objname = substring(@objname,1 , len(@objname)-1)
exec ('drop proc '+ @objname )
MohammedU
Microsoft SQL Server MVP
February 20, 2007 at 6:33 am
DECLARE
@cmd VARCHAR(max)
SET @cmd = ''
SELECT @cmd = @cmd + 'DROP PROCEDURE ' + ObjName + ';
' FROM (
SELECT QUOTENAME(ROUTINE_SCHEMA) + '.' + QUOTENAME(ROUTINE_NAME) AS ObjName
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_DEFINITION LIKE '%target string%'
) x
WHERE OBJECTPROPERTYEX(OBJECT_ID(ObjName), 'IsMSShipped') = 0
-- EXEC (@cmd)
PRINT @cmd
February 21, 2007 at 8:45 am
Very nice solution Scott, especially the detail of qualifying the object name!
Be aware, though, that INFORMATION_SCHEMA.ROUTINES only stores the first 4000 bytes of the SPROC text. If you are searching for specific text within a proc (that may be more than 4000 bytes into the code) it may be worth using sys.sql_modules as it stores all the source code as a nvarchar(MAX). (You would still need to jump through some hoops to get the owner name though.
SQL guy and Houston Magician
February 21, 2007 at 9:34 am
Okay, for people who create huge procedures:
DECLARE
@cmd VARCHAR(max)
SET @cmd = ''
SELECT @cmd = @cmd + 'DROP PROCEDURE ' + ObjName + ';
' FROM (
SELECT QUOTENAME(USER_NAME(o.schema_id)) + '.' + QUOTENAME(OBJECT_NAME(o.[object_id])) AS ObjName
FROM sys.objects o
INNER JOIN sys.sql_modules m ON m.[object_id] = o.[object_id]
WHERE o.[Type] = 'P' AND OBJECTPROPERTYEX(o.[object_id], 'IsMSShipped') = 0 AND m.definition LIKE '%target string%'
) x
-- EXEC (@cmd)
PRINT @cmd
February 21, 2007 at 9:52 am
Whoa, thanks for all the help. I've been working with Mohammed Uddin's code, which seems to be fitting the bill. I'm just having a little trouble writing the looping code around it (with all the requisite string parsing, etc) but I'll post the full code of what I came up with here in a day or two.
February 21, 2007 at 10:06 am
Umm, what looping code? His solution creates one command to drop them all at once. ("DROP PROC procedure1, procedure2, procedure3")
February 21, 2007 at 11:22 am
Aaron,
Do be aware that Mohammeds code could potentially overlook procs you wish to drop. You cannot always accuratly look for procs in syscomments by using a where clause such as 'WHERE text like '%my search string%'. I mean to disrespect to Mohammed; Mohammed's code is good, but there are some scenarios where it will not work the way you would expect it to.
You would be better off using Scott's code (the second version).
SQL guy and Houston Magician
February 21, 2007 at 6:44 pm
Hmm, well I guess I should have known that you could drop more than one item at a time. That would have saved me some work.
You know, I was wondering why you guys were only giving me "half" solutions. I thought maybe you had something against giving me the whole deal, sort of like a "RTFM you have to learn yourself" thing. But nope, it was great help all along.
Thanks a bundle.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply