April 15, 2009 at 9:59 am
I have a task to find all the stored procedures that contain a specific hard coded string. I definitely don't want to open up every single stored procedure to find it out. Is there any tool or stored procedures that I can use for the task?
April 15, 2009 at 10:07 am
Something like
select * from sys.sql_modules where definition like '%your string here%'
should get it for you.
April 15, 2009 at 10:12 am
this finds any object that references a specific string...just limit it to procedures int he where statement:
DECLARE @PhraseToFind varchar(250)
SET @PhraseToFind='syscolumns'
select obj.name,obj.xtype,
case obj.xtype
when 'TR' then 'Trigger'
when 'P' then 'Procedure'
when 'V' then 'View'
when 'TF' then 'Function'
when 'IF' then 'Function'
when 'FN' then 'Function'
else 'Unknown'
end
,c.text
from dbo.syscomments c
INNER JOIN dbo.sysobjects obj
on obj.id = c.id
where
patindex('%'+@PhraseToFind+'%',text) != 0
Lowell
April 15, 2009 at 10:28 am
Both worked perfect. Thank you guys.
April 15, 2009 at 10:37 am
Here is a stored procedure written by Narayana Vyas Kondreddi that I use to find a specific string in all the stored procedures and functions in a database.
create procedure dbo.sp_search_code
--drop procedure dbo.sp_search_code
(
@SearchStr varchar(100),
@RowsReturnedint = NULLOUT
)
AS
/*************************************************************************************************
Copyright © 1997 - 2002 Narayana Vyas Kondreddi. All rights reserved.
Purpose:To search the stored proceudre, UDF, trigger code for a given keyword.
Written by:Narayana Vyas Kondreddi
Tested on: SQL Server 7.0, SQL Server 2000
Date created:January-22-2002 21:37 GMT
Date modified:February-17-2002 19:31 GMT
Email: vyaskn@hotmail.com
Examples:
To search your database code for the keyword 'unauthorized':
EXEC sp_search_code 'unauthorized'
To search your database code for the keyword 'FlowerOrders' and also find out the number of hits:
DECLARE @Hits int
EXEC sp_search_code 'FlowerOrders', @Hits OUT
SELECT 'Found ' + LTRIM(STR(@Hits)) + ' object(s) containing this keyword' AS Result
*************************************************************************************************/
BEGIN
SET NOCOUNT ON
SELECTDISTINCT USER_NAME(o.uid) + '.' + OBJECT_NAME(c.id) AS 'Object name',
CASE
WHEN OBJECTPROPERTY(c.id, 'IsReplProc') = 1
THEN 'Replication stored procedure'
WHEN OBJECTPROPERTY(c.id, 'IsExtendedProc') = 1
THEN 'Extended stored procedure'
WHEN OBJECTPROPERTY(c.id, 'IsProcedure') = 1
THEN 'Stored Procedure'
WHEN OBJECTPROPERTY(c.id, 'IsTrigger') = 1
THEN 'Trigger'
WHEN OBJECTPROPERTY(c.id, 'IsTableFunction') = 1
THEN 'Table-valued function'
WHEN OBJECTPROPERTY(c.id, 'IsScalarFunction') = 1
THEN 'Scalar-valued function'
WHEN OBJECTPROPERTY(c.id, 'IsInlineFunction') = 1
THEN 'Inline function'
END AS 'Object type',
'EXEC sp_helptext ''' + USER_NAME(o.uid) + '.' + OBJECT_NAME(c.id) + '''' AS 'Run this command to see the object text'
FROMsyscomments c
INNER JOIN
sysobjects o
ON c.id = o.id
WHEREc.text LIKE '%' + @SearchStr + '%'AND
encrypted = 0AND
(
OBJECTPROPERTY(c.id, 'IsReplProc') = 1OR
OBJECTPROPERTY(c.id, 'IsExtendedProc') = 1OR
OBJECTPROPERTY(c.id, 'IsProcedure') = 1OR
OBJECTPROPERTY(c.id, 'IsTrigger') = 1OR
OBJECTPROPERTY(c.id, 'IsTableFunction') = 1OR
OBJECTPROPERTY(c.id, 'IsScalarFunction') = 1OR
OBJECTPROPERTY(c.id, 'IsInlineFunction') = 1
)
ORDER BY'Object type', 'Object name'
SET @RowsReturned = @@ROWCOUNT
END
April 15, 2009 at 10:55 am
Lowell (4/15/2009)
this finds any object that references a specific string...just limit it to procedures int he where statement:
DECLARE @PhraseToFind varchar(250)
SET @PhraseToFind='syscolumns'
select obj.name,obj.xtype,
case obj.xtype
when 'TR' then 'Trigger'
when 'P' then 'Procedure'
when 'V' then 'View'
when 'TF' then 'Function'
when 'IF' then 'Function'
when 'FN' then 'Function'
else 'Unknown'
end
,c.text
from dbo.syscomments c
INNER JOIN dbo.sysobjects obj
on obj.id = c.id
where
patindex('%'+@PhraseToFind+'%',text) != 0
Lowell and mtwrigz1
The code you provided might not work correctly if the SP's code is longer then 4000 and the searched text is split between two syscomments records.
I document my SPs heavily so I have a lot of them bigger then the 4000 chars and found out the hard way.:-)
I think it is safer to use sys.sys_modules instead and to link to sys.sys_procedures on objectid if SPs are needed only.
Another option is to have a UNION and search for the potentially split text using something like this
FROM syscomments AS L1
INNER JOIN syscomments AS L2 ON L1.id = L2.id
AND L1.colid = L2.colid - 1
WHERE RIGHT(L1.text, 50) + LEFT(L2.text, 50) LIKE (your search criteria here)
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
April 15, 2009 at 11:03 am
good point; i have a handfull of big functions that cross the 4000 char limit, i wonder just where exactly they split in the code....
Lowell
April 15, 2009 at 11:11 am
Lowell (4/15/2009)
i wonder just where exactly they split in the code....
By Murphy's law - right in the middle of the searched text....
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply