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?

  • 3
    Perhaps you didn't know but when you get an answer that actually helps you out, you have the option of accepting it formally. You can do so for this question as well as for any or all of your past questions.
    – Andriy M
    Dec 30, 2015 at 13:06

2 Answers 2


To do it through code...

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

FROM   sys.databases
         WHEN state_desc = 'ONLINE' 
              THEN OBJECT_ID(QUOTENAME(name) + '.[dbo].[costomersHR]', 'U')

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

  • @Martin, i can't understand how this case statement work .. can you help me to understand it.. but yes its more than amazing query to find a object.
    – AA.SC
    Dec 30, 2015 at 11:06
  • 1
    First of all, it checks when databases are in ONLINE state and when it's true it tries to return OBJECT_ID connected to table, which we are looking for. If it returns not-null value, table called [dbo].[costomersHR] is found and names of databases are returned for matched rows. Dec 30, 2015 at 11:21
  • @PawełTajs how this case statement retrieve list of all objects against each DB?
    – AA.SC
    Dec 30, 2015 at 11:27
  • 2
    @AA.SC - It doesn't. It just tests for the existence of a specific object. [dbname].[dbo].[costomersHR] for each online database by substituting in the database name. The OBJECT_ID function returns NULL if it doesn't exist. Dec 30, 2015 at 11:30
  • @MartinSmith, Can i modify this query to search DB names on instance contain schema 'HR' ?
    – AA.SC
    Jan 14, 2016 at 14:18

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

enter image description here


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.