Technical Article

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)

You rated this post out of 5. Change rating

Share

Share

Rate

2.82 (11)

You rated this post out of 5. Change rating