Sql-server – How to find a table in the whole instance

instancesql server

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?

Best Answer

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