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?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
To do it through code...
1) If you can assume that this table is in the
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
You can use this tool RedGate SQL Search. It works really great.