Script to find the database of a given procedure

Below script will search for all the databases and lists the name(s) of the database which the stored procedure exists.

----------------------------------------------------------------------
CREATE TABLE #DBNames (DBNo int IDENTITY(1,1), dbname varchar(200))

DECLARE @DBNo int, @MaxDBNo int, @SQLText varchar(2000)

INSERT INTO #DBNames(dbname)
SELECT name
FROM sys.databases

SELECT @MaxDBNo = MAX(DBNo), @DBNo = 1
FROM #DBNames
WHILE @DBNo < @MaxDBNo
BEGIN
SELECT @SQLText = 'IF EXISTS (SELECT ''' + dbname + ''',name FROM ' + dbname + '.sys.procedures WHERE name = ''usp_NameofTheProcedure'') SELECT ''' + dbname + ''''
FROM #DBNames
WHERE DBNo = @DBNo

EXEC(@SQLText)

SET @DBNo = @DBNo + 1
END

DROP TABLE #DBNames
----------------------------------------------------------------------