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?
-
3Perhaps 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 MDec 30, 2015 at 13:06
Add a comment
|
2 Answers
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
answered Dec 30, 2015 at 10:06
-
@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.SCDec 30, 2015 at 11:06
-
1First of all, it checks when databases are in ONLINE state and when it's true it tries to return
OBJECT_IDconnected 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.SCDec 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. TheOBJECT_IDfunction returnsNULLif 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.SCJan 14, 2016 at 14:18
You can use this tool RedGate SQL Search. It works really great.
