Find a table across multiple databases SQL SERVER 2005

Question

I exported a table to a server but I can't find the table. Maybe I didn't put the right destination database. How can I find this table if my server has multiple databases, without opening each one of them?

I use MS Sql Server Management Studio 2008.

Answer 1

select 'select * from '+name+'.sys.tables where name=
        ''[yourtable]'';' from sys.databases

Instead of [yourtable], type the name of the missing table, and run the result again.

Answer 2

Rough and dirty, but it would do the job.

sp_MSforeachdb 'USE ?
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N''[table_name_here]'') AND OBJECTPROPERTY(id, N''IsUserTable'') = 1)
BEGIN
  PRINT ''Found in db ?''
END'

Answer 3

EXEC sp_MSForEachDB '
USE ? 
IF OBJECT_ID(''mytable'') IS NOT NULL AND
   OBJECTPROPERTY(OBJECT_ID(''mytable''), ''IsTable'') = 1
    PRINT ''?''
'