How to find all Stored Procedures having a given text inside

If you want to search for all stored procedures having a given text inside, you may use the following script:

SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%__CS_%'
AND ROUTINE_TYPE='PROCEDURE'

This is the equivalent of running the following query directly against the system tables/views:

SELECT sys.sysobjects.name, sys.syscomments.text
FROM sys.sysobjects INNER JOIN syscomments
ON sys.sysobjects.id = sys.syscomments.id
WHERE sys.syscomments.text LIKE '%KEYWORD%'
AND sys.sysobjects.type = 'P'
ORDER BY sys.sysobjects.NAME

The INFORMATION_SCHEMA.ROUTINES view can be used to search for content of functions as well.  Simply alter your WHERE clause as follows, substituting your search string in place of "KEYWORD" once again:

SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%KEYWORD%'
AND ROUTINE_TYPE='FUNCTION'
ORDER BY ROUTINE_NAME

To highlight the fact that this process is backward-compatible to Microsoft SQL Server 2000, I've run the following query against the Northwind database.  I'm interested in finding all the stored procedures that utilize the ROUND() function:

USE Northwind
GO
SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%ROUND%'
AND ROUTINE_TYPE='PROCEDURE'
ORDER BY ROUTINE_NAME
GO