We have a lot of databases and a lot of tables within those databases. I'm searching for a specific one. I know the name of the table but it wouldn't be easy to search through every database manually. What SQL statement could I used to find the table by name?
Btw, we're using Microsoft SQL Server Management Studio. Maybe there's another way to search for tables by name within this program?
Thought I would update with the solution I use now to find a table among many dBs. After some searching around I found this query:
/*Finds a table across multiple dBs and returns the dB(s) in which the table was found*/
SELECT DISTINCT DB_NAME(database_id)
FROM [sys].[dm_db_index_operational_stats](NULL,NULL,NULL,NULL)
WHERE OBJECT_NAME(object_id,database_id) = 'table name'
This query finds the dB which holds the table. Then, in Microsoft SQL Server Mgmt Studio, I go to Object Explorer Window, find the dB identified by the query, expand its contents, and click on the Tables folder. Then I use the Filter tool to find the table by name. It would be nice if the filter tool worked on the Databases folder but it does not. You must select the Tables folder before filtering.
This may not be the best solution, but it works for me.