November 9, 2009 | SQL Server

Finding stored procedures containing %string%

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.

17 comments on this post

    • Uri Dimant - November 9, 2009, 9:53 PM

      Thanks Aaron,
      very nice script.

    • Chris - November 10, 2009, 12:32 AM

      Any chance you would share your utility procs.  I would be very interested.  I use AutoHotKey to store things like:
      select column_name, data_type, character_maximum_length, is_nullable from information_schema.columns
       where table_name = "
         and column_name like '%%'
           order by ordinal_position, column_name

    • Madhivanan - November 10, 2009, 12:27 PM
    • Alberto - November 10, 2009, 12:29 PM

      Thanks for the scripts.
      I use a free tool from xSQL software called SQL Server Object Search (http://www.xsqlsoftware.com/Product/Sql_Database_Object_Search.aspx) which works well both in 2005 and 2008.

    • casm - November 10, 2009, 1:29 PM

      As Albert says, there's a couple of free tools than can be installed as an Add-On on SSMS to do this kind of things.
      Take a look at Quick Find (http://ssmsaddins.codeplex.com/Release/ProjectReleases.aspx?ReleaseId=18363) or Object Finder (http://ssmsaddins.codeplex.com/Release/ProjectReleases.aspx?ReleaseId=18135)

    • Smitha Reddy - November 10, 2009, 4:46 PM

      Thanks for sharing Aaron – I've added to my collection.  I always keep my queries aka DBA toolset in one folder/organized so I don't have to run around when I really need them. The version I use:
      SELECT OBJECT_SCHEMA_NAME ([object_id]),object_name([object_id])
      FROM sys.sql_modules
      WHERE [definition] LIKE '%SearchPhrase%'

    • AaronBertrand - November 11, 2009, 2:38 AM

      Add-ins are an option for a lot of people.  Sometimes though you cannot install add-ins on all machines.

    • Madhivanan - November 11, 2009, 10:04 AM

      I agree with Aaron. Most of the times, you dont need any add-ons when the things can be done easily with queries

    • AaronBertrand - November 12, 2009, 7:42 PM

      Red-Gate just announced a new add-in that handles searching objects for %string%:
      http://www.red-gate.com/products/sql_search/index.htm

    • John Vanda - November 16, 2009, 11:30 PM

      Thanks for the great article Aaron!  I find myself using queries just like that all the time.  Any reason you prefer object_schema_name and object_name functions over joining to sys.schemas and sys.objects for the name columns?

    • Aaron Bertrand - November 16, 2009, 11:40 PM

      Sure John, I have two reasons:
      1. the joins tend to create much more verbose code.  In more complicated queries I can easily see the need to go to those tables several times.
      2. When I'm coding against instance-wide DMVs (e.g. missing index details) it is much easier to add the optional database_id parameter than to hardcode databasename.sys.objects, etc.
      HTH,
      Aaron

    • Raj - November 18, 2009, 5:43 AM

      I have the habit of using syscomments for 2k. Useful to track down triggers as well.
      I understand that sys.procedures is safer way to access as it is a catalog view and its not likely to change with patch installations.
      Any other adv favouring sys.procedures.

    • David - November 18, 2009, 5:09 PM

      You can also search for text in objects using SQLMetaTool which also has a lot of other functions such as table & index info. http://www.aldexsoftware.co.uk/SQLMetaTool

    • Stork - December 18, 2009, 1:35 AM

      I build my utility scripts in the master database so I can execute them from any database.  Use the undocumented "exec sp_MS_marksystemobject your_procedure_name".  If you want to get way crazy, see how synonyms (new in 2005) let you run your utilities on any server\instance you have access to (so you don't have to muck-up production code with utility scripts)

    • Shawkat - November 20, 2012, 1:49 PM

      Wow!! Indeed a Fantastic Article..!!
      Now I know- I don't know a loooot.. 😛
      First step of learning..!!!

    • Bikram Pattanayak - April 25, 2013, 2:45 PM

      Awaesome…

    • Bhoopendra Sharma - May 29, 2015, 1:25 PM

      Very useful Article, Saved my lot of time.
      Thanks a ton!!!

Comments are closed.