Search Stored Procedures
by Jon Hibbins on 5 December 2008
Filed under: Code, Database, MSSQL2005, MSSQL2008, Objective-C, Quick Tips, SQL Server, TSQL, iPhone
Ever had the need to search stored procedures to find redundant tables or text that needs replacing or re-factoring ?
Here’s a script to help (Sorry for the cursor, I’m sad with myself for using it 
)
-- Search string DECLARE @SearchString nvarchar(50) SET @SearchString = 'Search String' -- Required Declares DECLARE @getdbname sysname DECLARE @sqlstm nvarchar(1000) DECLARE SeachCursor cursor FOR -- Get All The Names into the SeachCursor SELECT '['+name+']' FROM [master].[dbo].[sysdatabases] ORDER BY name OPEN SeachCursor -- Add the Search Pattern SET @SearchString = '%' + @SearchString + '%' --Get the first Name FETCH NEXT FROM SeachCursor INTO @getdbname WHILE @@FETCH_STATUS=0 BEGIN --set the statement to define the search condition, with variables SET @sqlstm = ' SELECT [SP].[Specific_Catalog] AS [Database_Name], [SP].[Routine_Name] AS [Stored Procedure Name],[SP].[Routine_Definition] AS [Routine_Definition] FROM '+ @getdbname+'.[Information_Schema].[Routines] AS [SP] WHERE PatIndex('+''''+@SearchString+''''+', [Routine_Definition]) > 0' --Execute the Query EXEC (@sqlstm) FETCH NEXT FROM SeachCursor INTO @getdbname END --Close the Cursor and Deallocate it from memory CLOSE SeachCursor DEALLOCATE SeachCursor