Stored Procedure Digger

Here's a handy little trick if you are digging out stored procedures from multiple databases and you aren't sure where they are (or don't want to dig for them). Paste this code into a SQL Server SSMS or Query Analyzer, change the value of @sprocName, change the output type to text ([CTRL] + T), execute the command then copy the output to another query window and execute that.

DECLARE @sprocName NVARCHAR(100)
SET @sprocName = 'sp_MyLostStoredProcedure'

/************************************************/

DECLARE @DbName sysname
DECLARE @command NVARCHAR(MAX)
DECLARE DbEnum CURSOR FAST_FORWARD FOR
SELECT [name] FROM sys.sysdatabases

OPEN DbEnum
FETCH NEXT FROM DbEnum INTO @DbName

WHILE @@FETCH_STATUS = 0
BEGIN

SET @command = N'USE ' + @DbName + '
GO
'
SET @command = @command + N'IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[' + @sprocName + ']'') AND type in (N''P'', N''PC''))
BEGIN'
SET @command = @command + N' PRINT ''' + @DbName + '.dbo.' + @sprocName + '''
'
SET @command = @command + N' EXEC sp_helptext ' + @sprocName + '
END
'
PRINT @command
FETCH NEXT FROM DbEnum INTO @DbName
END
CLOSE DbEnum
DEALLOCATE DbEnum