Search for an object across multiple databases on a server.

Declare @searchKey varchar (100)
Declare @SearchObjectNameSql nvarchar (1000)
Declare @SearchObjectDefinitionSql nvarchar (1000)
DECLARE @SearchObjectDefinition bit
DECLARE @DbName sysname 

/*Do not set DBName to search across all databases*/--SELECT @DbName=DB_NAME() 

SET @searchKey='SearchObjectName'

SET @SearchObjectDefinition = 1
/*Change to 1 to search for SP text,Job step etc*/
SELECT
 @SearchObjectNameSql = 'USE [?];select ''?'' DB ,name,null,type_desc from sys.objects where name like ''%@searchKey%'''
SELECT
 @SearchObjectNameSql = REPLACE(@SearchObjectNameSql, '@searchKey', @searchKey)
SELECT
 @SearchObjectDefinitionSql = 'USE [?];select DISTINCT ''?'' DB ,so.name,
m.[definition],
so.type_desc
FROM 
sys.sql_modules m
join
sys.objects so
ON m.object_id=so.object_id
WHERE m.[definition] like ''%@searchKey%''
'
SELECT
 @SearchObjectDefinitionSql = REPLACE(@SearchObjectDefinitionSql, '@searchKey', @searchKey)


Declare @List Table
(
DBName varchar (500),
ObjectName varchar (1000),
ObjectDefinition nvarchar (max),
Type varchar (100)
)

IF @DbName IS NULL
BEGIN
  INSERT @List
  EXECUTE master.sys.sp_MSforeachdb @SearchObjectNameSql

IF @SearchObjectDefinition = 1
BEGIN
INSERT @List
    EXECUTE master.sys.sp_MSforeachdb @SearchObjectDefinitionSql
END
END
ELSE
BEGIN
  select @SearchObjectNameSql=replace(@SearchObjectNameSql,'?',@DbName)

  select @SearchObjectDefinitionSql=REPLACE(@SearchObjectDefinitionSql,'?',@DbName)
  
  INSERT @List
  execute sp_executesql @SearchObjectNameSql

  IF @SearchObjectDefinition = 1
  BEGIN
  INSERT @List
  execute sp_executesql @SearchObjectDefinitionSql
  END
END
IF EXISTS (SELECT
 1
FROM sys.databases
WHERE name = 'msdb')
BEGIN
INSERT @List
 SELECT
  'msdb',
  j.name,
  js.command,
  'SQL_JOB'
 FROM msdb.dbo.sysjobs j
 JOIN msdb.dbo.sysjobsteps js
  ON js.job_id = j.job_id
 JOIN master.dbo.sysservers s
  ON s.srvid = j.originating_server_id
 WHERE js.command LIKE N'%' + @searchKey + '%'
END



IF @SearchObjectDefinition = 0
BEGIN
SELECT
 DBName,
 ObjectName,
 Type
FROM @List
ORDER BY DBName, ObjectName
END
ELSE
BEGIN
SELECT
 DBName,
 ObjectName,
 ObjectDefinition,
 CASE
  WHEN ObjectDefinition IS NULL THEN Type + '_NAME' ELSE Type + '_DEFINITION'
 END
 Type
FROM @List
ORDER BY DBName, ObjectName
END