How do I find a stored procedure containing <text>?

sql-server sql-server-2008 tsql stored-procedures
Gary Kindel picture Gary Kindel · Feb 22, 2011 · Viewed 642.4k times · Source

I need to search a SQL server 2008 for stored procedures containing where maybe the name of a database field or variable name.

Answer

Kashif picture Kashif · Feb 22, 2011 
SELECT ROUTINE_NAME, ROUTINE_DEFINITION
    FROM INFORMATION_SCHEMA.ROUTINES 
    WHERE ROUTINE_DEFINITION LIKE '%Foo%' 
    AND ROUTINE_TYPE='PROCEDURE'

SELECT OBJECT_NAME(id) 
    FROM SYSCOMMENTS 
    WHERE [text] LIKE '%Foo%' 
    AND OBJECTPROPERTY(id, 'IsProcedure') = 1 
    GROUP BY OBJECT_NAME(id)

SELECT OBJECT_NAME(object_id)
    FROM sys.sql_modules
    WHERE OBJECTPROPERTY(object_id, 'IsProcedure') = 1
    AND definition LIKE '%Foo%'

Related questions

Current executing procedure name

Is it possible to get the name of the current Stored Procedure in MS SQL Server? Maybe there is a system variable or function like GETDATE()?

Read More
Currently running query inside a stored procedure

I have a stored procedure that is currently running, and seems to hang/lock on a specific query. How can i see which query? Preferably without modifying the proc. Using DBCC Inputbuffer (65) gives me Language Event 0 EXEC mySP;

Read More
How can I get column names from a table in SQL Server?

I want to query the name of all columns of a table. I found how to do this in: Oracle MySQL PostgreSQL But I also need to know: how can this be done in Microsoft SQL Server (2008 in my case)?

Read More