Sometimes you have to face a database without documentation.
Sometimes you have so many stored procedures that you don’t know where they are used.
To be able to search in the content of stored procedures or jobs is useful in this case and many others.

Stored Procedures

In SQL Server 2000 every database object is indexed in the sys.sysobjects of the same database, while the content is contained in sys.syscomments.
Since SQL Server 2005 you should use respectively sys.objects and sys.sql_modules.

Therefore, to search in this content you just have to

/** SQL Server 2000 **/
SELECT DISTINCT name, type_desc
FROM sysobjects O
  JOIN syscomments C
ON ( O.id = C.id )
WHERE text LIKE '%%'
ORDER BY name

/** SQL Server 2005/8 **/
SELECT DISTINCT name, type_desc
FROM sys.objects O
  JOIN sys.sql_modules SM
ON ( O.object_id = SM.object_id )
WHERE definition LIKE '%%'
ORDER BY name

On every database

You may be so confused that you don’t even know in which database you have to search.

If this is the case, a simple cursor will save the day

/** SQL Server 2000 **/
DECLARE c_database CURSOR
FOR SELECT name FROM MASTER..sysdatabases
--------
DECLARE @name AS VARCHAR(100)
DECLARE @sql as nvarchar(500)
DECLARE @string as nvarchar(100)
--string to search
SET @string = ''
-------
OPEN c_database
FETCH NEXT FROM c_database INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
  EXEC ('USE ['+@name+']
    SELECT DISTINCT name AS '+@name+',
      type
    FROM sysobjects O
      JOIN syscomments C
    ON ( O.id = C.id )
    WHERE text LIKE ''%'+@string+'%''')
  FETCH NEXT FROM c_database INTO @name
END 
CLOSE c_database
DEALLOCATE c_database

/** SQL Server 2005/8 **/
DECLARE c_database cursor
for select name from master..sysdatabases
--------
DECLARE @name as varchar(100)
DECLARE @sql as nvarchar(500)
DECLARE @string as nvarchar(100)
--string to search
SET @string = ''
-------
OPEN c_database
FETCH NEXT FROM c_database INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
  EXEC ('USE ['+@name+']
    SELECT DISTINCT name AS '+@name+',
      type_desc
    FROM sys.objects O
      JOIN sys.sql_modules SM
    ON ( O.object_id = SM.object_id )
    WHERE definition LIKE ''%'+@string+'%''')
  FETCH NEXT FROM c_database INTO @name
END
CLOSE c_database
DEALLOCATE c_database

Search in Jobs

Searching in job steps code can be particulary useful when you are looking for places where stored procedures are called (apart from other procedures).
Since jobs are not referred to any particular database, the informations you need is stored entirely in the system database msdb. The system tables you are going to use are sysjobs and sysjobsteps, that contains respectively jobs informations and step code content.

SELECT SJ.name, SJS.step_id
FROM msdb.dbo.sysjobs SJ
  JOIN msdb.dbo.sysjobsteps SJS
ON ( SJ.job_id = sjs.job_id )
where command LIKE '%%'