USE [master]
GO
CREATE PROCEDURE sp_SearchProcs
(
@SearchText VARCHAR(1000)
)
AS
BEGIN
DECLARE curDB CURSOR FORWARD_ONLY STATIC FOR
SELECT [name]
FROM master..sysdatabases
WHERE [name] NOT IN ('model', 'tempdb')
ORDER BY [name]
DECLARE @DB sysname
OPEN curDB
FETCH NEXT FROM curDB INTO @DB
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @SQL NVARCHAR(MAX) = 'USE [' + @DB +'] SELECT Distinct SO.Name, SC.Text, SS.Name AS [Schema], ''' + @DB + ''' AS [Database] FROM sysobjects SO (NOLOCK) INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID INNER JOIN sys.objects SO2 (NOLOCK) ON SO.id = SO2.object_id INNER JOIN sys.schemas SS ON SO2.schema_id = SS.schema_id WHERE SO.Type = ''P'' AND SC.Text LIKE ''%' + REPLACE(@SearchText, '''', '''''') + '%'' ORDER BY SO.Name'
BEGIN TRY
exec sp_executesql @SQL
END TRY
BEGIN CATCH
END CATCH
FETCH NEXT FROM curDB INTO @DB
END
CLOSE curDB
DEALLOCATE curDB
END
Here's how to use it:
sp_SearchProcs 'insert%into%mytable'
