Searching a table in several databases

Question

How can I figure out a table (say tbl_mytable) lies in which database?

Answer 1

If you're using SQL Server 2005 or above and this is an adhoc thing, rather than a programmatic thing, you can try SQL Search by Red Gate. It's a free Management Studio plugin.

Edit: Programmatically, you can do it via sp_MSForEachDB which is an undocumented system procedure - so, functionality is not guaranteed to behave the same or even exist between releases (i.e. don't use in production code).

EXECUTE sp_msforeachDB 'IF EXISTS(
    Select 1 From [?].INFORMATION_SCHEMA.Tables where TABLE_NAME = ''tbl_mytable'') 
    PRINT ''?'''

This will print a list of database names which contain the table.

Answer 2

I have got partial solution like this:

Select Table_Name From DatabaseName.INFORMATION_SCHEMA.Tables where TABLE_NAME like 'tbl_Mytable'

But the above query will search the table in only one database.