It is really useful to be able to find a table or stored procedure with a particular string in it; and it’s actually really simple.
For the example of a Stored Procedure, the contents are stored in INFORMATION_SCHEMA.ROUTINES and also syscomments and either can be queried:
SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE ‘%foobar%’
AND ROUTINE_TYPE=’PROCEDURE’SELECT OBJECT_NAME(id)
FROM syscomments
WHERE [text] LIKE ‘%foobar%’
AND OBJECTPROPERTY(id, ‘IsProcedure’) = 1
GROUP BY OBJECT_NAME(id)
In the second example GROUP BY is used because the stored procedures are stored in rows of up to 8000 characters so if the same procedure contains the search phrase in more than one row, only one reference is returned. This quirk does mean that the the search phrase could be split over more than one row, and therefore not returned in this search, so if you have very long procedures where this is likely a more complex search would need to be used which first concatenates the contents of the fields.
Tags: Database, objects, searching, SQL, SQL Server, stored proceures