How to find stored procedure which contains given string

Question

I have many store procedure created in my databse. but i need to search stored procedure which contains 'welcome to' in its definition. I can check each stored procedure by sp_helptext I have many stored procedure So i cant check each store procedure using sp_help because its time consuming

Please help if can

Answer 1

you can use System view SYS.SQL_MODULES and SYS.OBJECTS I have created stored procedure to find any text within any type of object

check it below

CREATE PROCEDURE [SearchFromObjects]
(
    @SearchString NVARCHAR(500) = NULL,
    @SearchObjectList VARCHAR(50) = NULL
)
AS
BEGIN
SET NOCOUNT ON
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
    DECLARE @sqlQuery NVARCHAR(1000)
    SELECT @SearchObjectList = ''''+REPLACE(@SearchObjectList, ',', ''',''')+''''

    IF ISNULL(@SearchString, '') <> ''
    BEGIN
        SELECT @sqlQuery = ' SELECT SYS_OBJ.NAME AS [OBJECT_NAME],
                                CASE 
                                    WHEN SYS_OBJ.TYPE = ''P''
                                        THEN ''PROCEDURE''
                                    WHEN SYS_OBJ.TYPE = ''TR''  
                                        THEN ''TRIGGER''
                                    WHEN SYS_OBJ.TYPE = ''FN''  
                                        THEN ''SCALAR FUNCTION''
                                    WHEN SYS_OBJ.TYPE = ''TF''  
                                        THEN ''TABLE-VALUED FUNCTION''
                                    WHEN SYS_OBJ.TYPE = ''IF''  
                                        THEN ''INLINE TABLE-VALUED FUNCTION''
                                    WHEN SYS_OBJ.TYPE = ''V''   
                                        THEN ''VIEW''                               
                                END [OBJECT_TYPE]
                             FROM SYS.SQL_MODULES SYS_MOD
                             INNER JOIN SYS.OBJECTS SYS_OBJ ON SYS_OBJ.OBJECT_ID = SYS_MOD.OBJECT_ID
                             WHERE SYS_MOD.DEFINITION LIKE ''%'+@SearchString+'%''' +
                             CASE 
                                WHEN ISNULL(@SearchObjectList, '') <> ''
                                    THEN ' AND SYS_OBJ.TYPE IN('+@SearchObjectList+') '
                             ELSE '' END +
                             ' ORDER BY SYS_OBJ.TYPE '
    END                          

    IF(ISNULL(@SearchString, '') = '')
    BEGIN
        PRINT 'Error : Incorrect Syntax...!!!      '
        PRINT '                                   ' 
        PRINT '-----------------------------------'
        PRINT 'Procedure Can take two arguments   '
        PRINT '-----------------------------------'
        PRINT '[1] Search String       : string to search in object defination (Required)'
        PRINT '         Syntax      : EXEC [SearchFromObjects] ''<<String to search>>'''
        PRINT '         For Example : EXEC [SearchFromObjects] ''Welcome to'''
        PRINT '[2] Search Object type  : list of object type in which user want to search (Optional)'
        PRINT '         Syntax      : EXEC [SearchFromObjects] ''<<String to search>>'', ''<<List of object type seperated by comma>>'''
        PRINT '         For Example : EXEC [SearchFromObjects] ''Welcome to'', ''P,TR,FN,TF,IF,V'''
        PRINT '                                       ' 
        PRINT 'List of allowed object types'
        PRINT '---------------------------------------'
        PRINT ' TYPE     DESCRIPTION                  '
        PRINT '---------------------------------------'
        PRINT ' P        PROCEDURE                    '
        PRINT ' TR       TRIGGER                      '
        PRINT ' FN       SCALAR FUNCTION              '
        PRINT ' TF       TABLE-VALUED FUNCTION        '
        PRINT ' IF       INLINE TABLE-VALUED FUNCTION '
        PRINT ' V        VIEW                         '
        PRINT '---------------------------------------'
    END
    ELSE
    BEGIN
        EXECUTE(@sqlQuery)  
    END
SET NOCOUNT OFF
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON                                         
END
GO

Just pass two parameters 1) string ('Welcome to') and 2) Object type ('P' for procedure) then you will get the stored procedure name which contain your search string

EXEC [SearchFromObjects] 'Welcome to', 'P'

you can execute [SearchFromObjects] without any parameter to get more help

Answer 2

Use INFORMATION_SCHEMA.ROUTINES.

SELECT *
FROM   INFORMATION_SCHEMA.ROUTINES
WHERE  ROUTINE_DEFINITION LIKE '%welcome to%'
       AND ROUTINE_TYPE = 'PROCEDURE' 

or use sys.sql_modules

SELECT sm.object_id,
       Object_name(sm.object_id) AS object_name,
       sm.definition
FROM   sys.sql_modules AS sm
       JOIN sys.objects AS o
         ON sm.object_id = o.object_id
WHERE  type_desc = 'SQL_STORED_PROCEDURE'
       AND definition LIKE'%welcome to%'