Search T-SQL stored procedures containing text string.
I've written thousands of stored procedures that I barely remember. So, as you can imagine, sometimes I'd like to search an entire database for a specific text string. Here's a simple SQL query that will accomplish this:
SELECT OBJECT_NAME(id)
FROM syscomments
WHERE [text] LIKE '%searchtext%' AND OBJECTPROPERTY(id, 'IsProcedure') = 1
GROUP BY OBJECT_NAME(id)
I've used this with success in management studio for SQL 2008, SQL 2005, and through query analyzer in SQL 2000.
Tags: Database, dba, management studio, query, query analyzer, sql 2000, sql 2005, sql 2008, stored procedure, syscomments