7

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 '15 at 13:06
17

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

  • @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 '15 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. – Paweł Tajs Dec 30 '15 at 11:21
  • @PawełTajs how this case statement retrieve list of all objects against each DB? – AA.SC Dec 30 '15 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. – Martin Smith Dec 30 '15 at 11:30
  • @MartinSmith, Can i modify this query to search DB names on instance contain schema 'HR' ? – AA.SC Jan 14 '16 at 14:18
5

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, privacy policy and cookie policy

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