SQL

sql server search all databases for objects

DECLARE @dbExclList VARCHAR(1000)
DECLARE @query VARCHAR(2000)
DECLARE @command VARCHAR(2000)

SET @dbExclList = 'NOT IN (''tempdb'')'
SET @query = 'SELECT
	DB_NAME(obj.parent_object_id) AS DB,
	obj.name AS OBJ_NAME, 
    obj.type_desc, 
    SCHEMA_NAME (obj.schema_id ) AS schema_name
FROM
	sys.objects obj
WHERE
	upper(obj.name) like ''%SEARCHSTRING%''
'

SET @command = 'IF ''?'' ' + @dbExclList + ' BEGIN USE ? ' + @query + ' END' 

PRINT @command

EXEC sp_MSforeachdb @command1 = @command