ps_SearchStoredProcedures

-- =============================================
-- Create procedure basic template
-- =============================================
-- creating the store procedure
IF EXISTS (SELECT name 
   FROM   sysobjects 
   WHERE  name = N'ps_SearchStoredProcedures' 
   AND   type = 'P')
    DROP PROCEDURE ps_SearchStoredProcedures
GO

CREATE PROCEDURE ps_SearchStoredProcedures 
@SearchString varchar(255),
@WhichProcs varchar(255) = 'ps%'
AS
set nocount on
 
--DECLARE @SearchString varchar(255)

--set @SearchString = '%Console_Name%'

DECLARE @spcount int,
@spLoopCount int,
@proc_name varchar(200),
@spTextTotal int,
@spTextCount int

CREATE TABLE #tStoredProcs
(
num_ID INTEGER IDENTITY(1,1),
  PROCEDURE_QUALIFIER varchar(200),  
  PROCEDURE_OWNER varchar(200),  
  PROCEDURE_NAME varchar(200),  
  NUM_INPUT_PARAMS int,  
  NUM_OUTPUT_PARAMS int,  
  NUM_RESULT_SETS int, 
  REMARKS varchar(255),  
  PROCEDURE_TYPE varchar(10)
)  

CREATE TABLE #tStoredProcText
(
num_ID INTEGER IDENTITY(1,1)
,spText varchar(1000)
) 

CREATE TABLE #SummaryList
(
num_ID INTEGER IDENTITY(1,1),
PROCEDURE_NAME varchar(200)
)

INSERT INTO #tStoredProcs
    EXECUTE sp_stored_procedures @WhichProcs
SET @spcount = @@ROWCOUNT


if @spcount < 1 
   return  
    

--print @lngTabCount
SET @spLoopCount = 0

WHILE @spLoopCount <= @spcount
  BEGIN
    SET @spLoopCount = @spLoopCount + 1
--print @lngLoopCount
    SET @proc_name = (SELECT PROCEDURE_NAME FROM #tStoredProcs WHERE num_ID = @spLoopCount)
    
    SET @proc_name = SUBSTRING(@proc_name,1,PATINDEX('%;%',@proc_name)-1)
   
    TRUNCATE TABLE #tStoredProcText

    if @proc_name is not NULL 
       BEGIN
    INSERT INTO #tStoredProcText
EXEC sp_helptext @proc_name
     SET @spTextTotal = @@ROWCOUNT
     
    SET @spTextCount = 0

  WHILE @spTextCount <= @spTextTotal  
    BEGIN
set @spTextCount = @spTextCount + 1

if exists(select num_ID from #tStoredProcText where num_ID = @spTextCount and spText like @SearchString)
   BEGIN
              PRINT @proc_name+': Line: '+CONVERT(varchar(20),@spTextCount)+' :Search String Found'

      INSERT INTO #SummaryList (PROCEDURE_NAME)
  VALUES(@proc_name)
   END
    END
   END
END

select distinct(PROCEDURE_NAME) from #SummaryList

DROP TABLE #SummaryList

DROP TABLE #tStoredProcs

DROP TABLE #tStoredProcText

return
GO