How can I find a table in the whole instance?

Question

I am trying to create a report which selects data from a table called costomersHR. I cannot find it as we have hundreds of databases in the instance.

How can I search for a table in the whole instance?

Answer 1

To do it through code...

1) If you can assume that this table is in the dbo schema.

SELECT name
FROM   sys.databases
WHERE  CASE
         WHEN state_desc = 'ONLINE' 
              THEN OBJECT_ID(QUOTENAME(name) + '.[dbo].[costomersHR]', 'U')
       END IS NOT NULL 

2) Otherwise you could loop through all databases (and query sys.tables in each) with something like this code for a more reliable and flexible sp_MSforeachdb

Answer 2

You can use this tool RedGate SQL Search. It works really great.