How to grep SQL Server stored procedures?

Question

I would like to run standard grep on all stored procedures in a given SQL Server database (suppose 2005 or later). I found many simple queries to list the names of stored procedures containing a specific object, for example.

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

But what I really want, like grep, is to list specific lines in identified stored procedures (so I don’t need to manually open each of them and see that this is what I'm looking for).

I am open to solutions in T-SQL or PowerShell or even in a ready-made utility.

Answer 1

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

Answer 2

I am new to SQL Server, but it seems to work fine and has already established itself as useful. 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 related queries you might 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; 

Answer 3

You can use SMO to unload the DDL of the stored procedure into files, and then use grep for them.

There are several examples of DDL extraction here (called Scripting).