Does any one know the script to use for text search in SQL Server? I would like to search a text from all the stored proc inside the SQL Server, does anyone know what is the script I should use?
Add a comment
|
4 Answers
INFORMATION_SCHEMA.ROUTINES or syscomments are not reliable.
The text field is nvarchar(4000) for both (over multiple rows syscomments only). So your search text can be lost on the boundary for a syscomments or never found for INFORMATION_SCHEMA.ROUTINES
sys.sql_modules.definition is nvarchar(max)
SELECT
OBJECT_NAME(object_id)
FROM
sys.sql_modules
WHERE
definition LIKE '%mytext%'
Edit, Oct 2011
Bringing this answer up to date.
Red Gate SQL Search is a free SSMS plug in that is quite useful.
You can use as well:
select distinct object_name(id) from sys.syscomments where text like '%SearchTextHere%'
Updated: There are several equivalent ways. Here's one:
SELECT
OBJECT_NAME(object_id)
FROM
sys.sql_modules
WHERE
Definition LIKE '%searchtext%'
AND OBJECTPROPERTY(object_id, 'IsProcedure') = 1
5 Comments
gbn
-1 This is not equivalent to sys.sql_modules or OBJECT_DEFINITION because it may not work... the ROUTINE_DEFINITION may not have the while proc code
gbn
@Mitch Wheat: it was wrong when I posted: you just edited to correct it. You have down voted my correct answer...
Lieven Keersmaekers
@gbn - if you downvote this answer, you should have downvoted the syscomments answer to. The fact that you didn't makes it look very selective. I can understand Mitch being annoyed by that, I know I would be.
gbn
@Mitch Wheat, @Lieven: The implied trust in user with 76k rep makes it an authoritative answer, with more weight than an answer from a 300 rep user. It seems unfair to me to downvote the 300 rep user (when practically the answer will be ignored anyway). If Jon Skeet had posted a "syscomments" no amount of counter examples would help correct the mis-information. Anyway, downvote removed
Lieven Keersmaekers
@gbn: fair enough, I assumed the worst. Some defect in my character
