Having just returned from PASS, I needed to play catch up on a few things in a stored procedure that was being developed before I left.  Being a little frazzled still, I could not for the life of me remember the name of the stored procedure where those things existed.  I constantly find myself manually writing queries like this to find all references to a column, table, variable or concept:

    SELECT [name] 
    FROM sys.procedures 
    WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%string%';

or

    SELECT OBJECT_NAME([object_id])
    FROM sys.sql_modules 
    WHERE [definition] LIKE '%string%';

Yes, I could rely on dependencies *sometimes* – but even when they are not broken, I am not always trying to find things that show up in a dependency chain.

Now the queries above are a little tedious to write, but not too bad.  But there are other complications that can often add a little bit of effort:

• When dealing with multiple owners/schema this can be more complex, so I would always add:

    SELECT OBJECT_SCHEMA_NAME([object_id]) 
    + '.' + [name] ...

• And in the case of poorly chosen identifiers (hey, this isn't always against my own systems!), I would add QUOTENAME:

    SELECT QUOTENAME(OBJECT_SCHEMA_NAME([object_id]) 
    + '.' + QUOTENAME([name]) ...

• And of course I would want the results to allow me to quickly get at the definition for those stored procedures, so I would want sp_helptext as part of the results:

    SELECT 'EXEC sp_helptext ''' 
    + QUOTENAME(OBJECT_SCHEMA_NAME([object_id]) 
    + '.' + QUOTENAME([name]) + '''' ...

This all gets very tedious to type, so a while back I decided to implement a set of stock, utility procedures in all of my databases (and don't tell my clients, but I've added it to some of their systems, too).  I wanted one for procedures specifically, and then one that will search sql_modules so that it picks up functions and triggers as well.

    CREATE PROCEDURE dbo.Find_InSP
   @string NVARCHAR(MAX)
AS
BEGIN
   SET NOCOUNT ON;
 
   SELECT
       cmd = N'EXEC sp_helptext ''' 
           + QUOTENAME(OBJECT_SCHEMA_NAME([object_id]))
           + '.' + QUOTENAME([name]) + ''';'
   FROM
       sys.procedures
   WHERE
       OBJECT_DEFINITION([object_id]) LIKE N'%' + @string + '%'
   ORDER BY
       cmd;
END
GO
 
CREATE PROCEDURE dbo.Find_InModule
   @string NVARCHAR(MAX)
AS
BEGIN
   SET NOCOUNT ON;
 
   SELECT
       cmd = N'EXEC sp_helptext ''' 
           + QUOTENAME(OBJECT_SCHEMA_NAME([object_id]))
           + '.' + QUOTENAME(OBJECT_NAME([object_id])) + ''';'
   FROM
       sys.sql_modules
   WHERE
       [definition] LIKE N'%' + @string + '%'
   ORDER BY
       cmd;
END
GO

Now when I run:

    EXEC msdb.dbo.Find_InSP 'agent';

I get the following results (and as an added bonus, I can use CTRL in the grid results to copy the sp_helptext commands only for the procedures I am interested in):

I have also added the procedures to the model database, so that they exist in all future databases that are created as well.  Does this save me a ton of time on any given day?  Of course not.  But every shortcut helps, right?  I hope these procedures are useful for you.