DECLARE @SQL NVARCHAR(max)
,@spName VARCHAR(100) = 'PROC_Name'
SELECT @SQL = STUFF((
SELECT ' UNION ALL
SELECT ' + quotename(NAME, '''') + ' AS Db_Name FROM ' + quotename(NAME) + '.INFORMATION_SCHEMA.Routines
WHERE ROUTINE_NAME Like @spName AND ROUTINE_TYPE = ''PROCEDURE'''
FROM sys.databases
ORDER BY NAME
FOR XML PATH('')
,TYPE
).value('.', 'nvarchar(max)'), 1, 11, '')
Print @SQL
EXECUTE sp_executeSQL @SQL
,N'@spName varchar(100)'
,@spName
,@spName VARCHAR(100) = 'PROC_Name'
SELECT @SQL = STUFF((
SELECT ' UNION ALL
SELECT ' + quotename(NAME, '''') + ' AS Db_Name FROM ' + quotename(NAME) + '.INFORMATION_SCHEMA.Routines
WHERE ROUTINE_NAME Like @spName AND ROUTINE_TYPE = ''PROCEDURE'''
FROM sys.databases
ORDER BY NAME
FOR XML PATH('')
,TYPE
).value('.', 'nvarchar(max)'), 1, 11, '')
Print @SQL
EXECUTE sp_executeSQL @SQL
,N'@spName varchar(100)'
,@spName