Searching for Text Within a Stored Procedure

Here is a cool sp that will find any text within a stored procedure. Add this your Master database.

CREATE PROCEDURE [dbo].[Find_text_in_sp] @text VARCHAR(250),@dbname VARCHAR(64)




      SET nocount ON;

      IF @dbname IS NULL


            --enumerate all databases.  

            DECLARE #db CURSOR FOR

              SELECT name

              FROM   MASTER..sysdatabases

            DECLARE @c_dbname VARCHAR(64)

            OPEN #db

            FETCH #db INTO @c_dbname

            WHILE @@FETCH_STATUS <> 1 --and @MyCount < 500  


                  EXECUTE Find_text_in_sp



                  FETCH #db INTO @c_dbname


            CLOSE #db

            DEALLOCATE #db

        END --if @dbname is null  


        BEGIN --@dbname is not null  

            DECLARE @sql VARCHAR(250)

            --create the find like command  

            SELECT @sql = ‘select ”’ + @dbname +

                          ”’ as db,,m.definition ‘

            SELECT @sql = @sql + ‘ from ‘ + @dbname + ‘.sys.sql_modules m ‘

            SELECT @sql = @sql + ‘ inner join ‘ + @dbname +

                          ‘..sysobjects o on’

            SELECT @sql = @sql + ‘ where [definition] like ”%’ + @text + ‘%”’

            EXECUTE (@sql)

        END --@dbname is not null  


Tip By: David McCarter -orginal code from:

One thought on “Searching for Text Within a Stored Procedure

  1. That is a nice one..

    The following may be useful for someone

    Search for a database reference – when you dont know what database something is in…
    with the Following Statement

    Exec sp_MSforeachdb ‘Select ”?” as DBName, * From ?..sysobjects where name like ”%STRING YOU WANT TO SEARCH%”’

Comments are closed.