Sometimes you want to modify an object (like a stored procedure, Function, trigger
and view) and you can not
remember the name of that object, but you remember some
words of the comments that you placed in the body of that
object.
If this is your case, you can use the following procedures depending on what version of SQL Server you are using.
It will
search all the databases except system databases and if it finds any match, it will return the name and the type of that object.
By the way, this stored procedure is on its second edition.
Usage:
Consider you have the following comment in the body of your object:
"This is a test to test the efficiency of the stored proc!"
You can invoke the proc in the following ways:
Exec Usp_SearchInBodyOfCodes 'efficiency "This is a test" ', @flag='w'
It will search for the words "efficiency" and "This is a test" in the body of mentioned objects.
Exec Usp_SearchInBodyOfCodes 'efficiency "This is a test" ', @flag='a'
Will search for any word of "efficiency" or "This is a test" n the body of the objects
Consideration:
Best to view the results in Query Analyzer Results in Text (Control
+T)
you can limit the databases in which you want Have fun with it, and if you like it vote for it....
