Search for a String in all Stored Procedures and beyond?

,

This Code will help in a Quick Search for a STRING in Stored Procedure across all the Databases on a SQL Server INSTANCE.

--Declare whatever you can :)
Declare @SString nvarchar(50)
Declare @getdbname sysname
Declare @sqlstm nvarchar(1000)
Declare dbname cursor for 

--get all the names of the Databases in order by name
Select '['+name+']' from master.dbo.sysdatabases order by name 
open dbname

--Get the first Name
FETCH NEXT FROM dbname into @getdbname

WHILE @@FETCH_STATUS=0
BEGIN

--set the search string
SET @SString = '<Place the String Here>'

--append the search pattern
SET @SString = '%' + @searchString + '%'

--set the statement to define the search condition, with variables
SET @sqlstm = '
Select Specific_Catalog as Database_Name, Routine_Name as ''Stored Procedure Name'',Routine_Definition 
From '+ @getdbname+'.Information_Schema.Routines 
Where PatIndex('+''''+@SString+''''+', Routine_Definition) > 0'

--Execute the Query
EXEC (@sqlstm)
FETCH NEXT FROM dbname into @getdbname
END

--Close the Cursor and Deallocate it from memory
Close dbname
Deallocate dbname

Rate

2.82 (11)

Share

Share

Rate

2.82 (11)