Is there a way to search for text within stored procedures? For example I want to find out if a particular table is being referenced by any stored procedure.
+3
A:
Use:
SELECT OBJECT_NAME(m.object_id), m.*
FROM SYS.SQL_MODULES m
WHERE m.definition like N'%text_youre_looking_for%'
SYSCOMMENTS and INFORMATION_SCHEMA.routines have NVARCHAR(4000) columns. So if "text_youre_looking_for" is used at position 3998, it won't be found. SYSCOMMENTS does have multiple lines, but INFORMATION_SCHEMA.routines truncates.
OMG Ponies
2010-10-21 19:16:09
Thanks for setting me straight.
JNK
2010-10-21 19:16:51
@JNK: For the record, I didn't downvote you.
OMG Ponies
2010-10-21 19:17:24
@OMG - no biggie, I posted a wrong answer :)
JNK
2010-10-21 19:17:55
@JNK: I did but decided to do my answer first beore commenting
gbn
2010-10-21 19:18:31
@gbn: As I said, no biggie it was a wrong answer. that also explains an issue I had with that script in the past.
JNK
2010-10-21 19:19:28
Don't forget if you just care about references and you have the permissions you can just right click the sproc in Object Explorere and select View Dependencies. And this is in addition to what the others have said so please don't downvote me like some did earlier today on a different post when I was just giving additional information.
Mike Cheel
2010-10-21 19:32:31
@Mike Cheel: it's not reliable. Try dropping/recreating a base table used by a view. Dependencies are *not* maintained (though it's getting better)
gbn
2010-10-22 04:48:40
+6
A:
SELECT
OBJECT_SCHEMA_NAME(object_id) + '.' + OBJECT_NAME(object_id)
FROM
sys.sql_modules
WHERE
definition like '%whatever%'
syscomments is legacy and splits the definition into nvarchar 4000 chunks thus risking not finding what you want. The same applies to INFORMATION_SCHEMA.ROUTINES
gbn
2010-10-21 19:17:49
+1, `OBJECT_SCHEMA_NAME()` is way better than joining to sys.objects and then sys.schemas!!
KM
2010-10-21 19:24:55
A:
My co-worker graciously provided me with this one recently. It does some similar searching as others have noted, but with a little more added in.
DECLARE
@chvStringToFind varchar(256), /* String to find */
@chrObjectType char(2),--=null, /* Only look for objects of this type */
@intNbCharToExtract int
--=50 /* Number of characters to extract before and after the string to find */
--exec DBA_FindStringInDB @chvStringToFind='sp_helpdia', @chrObjectType=null, @intNbCharToExtract=50
SET @chvStringToFind = 'EnterSearchTextHere' -- Change this to search
SET @chrObjectType = NULL
SET @intNbCharToExtract = 50
SELECT t.Name, t.TypeDescription, t.CreationDate, t.ModificationDate,
'...' + SUBSTRING
(
t.ObjectDefinition,
CHARINDEX(@chvStringToFind, t.ObjectDefinition) - @intNbCharToExtract,
LEN(@chvStringToFind) + (@intNbCharToExtract*2)
) + '...' AS Extract
FROM
(
SELECT o.name AS Name,
o.type_desc AS TypeDescription,
o.create_date AS CreationDate, o.modify_date AS ModificationDate,
OBJECT_DEFINITION(object_id) AS ObjectDefinition
FROM sys.objects o
WHERE
((o.type IN ('AF', 'FN', 'IF', 'P', 'TF', 'TT', 'U', 'V', 'X') AND @chrObjectType IS NULL) OR o.type = @chrObjectType)
AND OBJECT_DEFINITION(o.object_id) LIKE '%' + @chvStringToFind + '%'
) AS t
ORDER BY TypeDescription, Name
Vinnie
2010-10-21 19:38:49
+2
A:
Red Gate has a free tool called Sql Search that I rather like. It keeps an index so that after its first search, it is very fast (and even the first one is pretty good...). It can search for text in procs as well as table and view definitions, etc. There are some filtering features to make it a little easier to use. And the search results are displayed in a very useful manner, with the full text of the object and the search text highlighted.
Ray
2010-10-21 19:54:12