ps_SearchStoredProcedures

,

ps_SearchStoredProcedures provides a "find utility" to search for string in Stored Procedure(s) that you select without dumping to a SQL script file. This stored procedure returns a list of the stored procedures that the string was found in and list of line numbers where the text was found.

Both parameters of the stored procedure allow for wildcards.
 
ps_SearchStoredProcedures
    @SearchString varchar(255),
    @WhichProcs varchar(255)
EX.
  EXEC ps_SearchStoredProcedures '%Bob%','%Search%'

  --> Searches for "Bob" in the Stored Procedures that have "Search" in the name of the stored procedure

-- =============================================
-- 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

Rate

Share

Share

Rate