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
----------------------------------------------------------------------
