How to grep SQL Server stored procedures?

Question

I would like to run a standard grep over all stored procedures in a given SQL Server database (assume 2005 or later). I have found a variety of simple queries to list the names of stored procedures containing a specific object, e.g.

SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%table_I_want_to_find%'

But what I really want is, like grep, to list the specific lines in the identified stored procedures (so I do not have to manually open each one and see if it is what I am looking for).

I am open to solutions in T-SQL or PowerShell, or even an off-the-shelf utility.

Answer 1

Use SQL Search from Red Gate. It's a free tool and is fantastic.

Answer 2

I am brand new to SQL Server, but the following seems to work great and has proved itself to be quite useful already. I am using SQL Server 2008.

select ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE, ROUTINE_BODY, ROUTINE_DEFINITION
from INFORMATION_SCHEMA.ROUTINES
where ROUTINE_DEFINITION like '%searchtext%'
order by ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE, ROUTINE_DEFINITION;

For completeness, here are some similar queries you'll probably want to know about:

exec sp_help 'myTable'

select name
from sys.all_views
order by name;

select TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE
from INFORMATION_SCHEMA.TABLES
order by TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE;

select TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE
from INFORMATION_SCHEMA.COLUMNS
where COLUMN_NAME = 'myColumn'
order by TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME;