SQL statement to find a table by its name

Question

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?

Solution 1

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.

Solution 2

You said you did a search which should've led you to this article:

http://blog.sqlauthority.com/2008/04/29/sql-server-find-table-in-every-database-of-sql-server/

If not, follow that. Basically what he creates is a stored procedure which will search for every table name you specify in every database.

If you were to do this:

select * from sys.tables where name like '%tablename%'

You would need to change the database every single time and if you have a lot, well you see the problem.

Solution 3

Try this:

Select name from DBname.sys.tables where name like '%info'

Comments

[navig8tr]

I did. I tried select * from sys.tables where name like '%database%', but it doesnt seem to be working for me.

[Jack Marchetti]

@navig8tr the SO community has become quite sensitive recently. Next time post some code with your question first. I see you did in the comment above at least

[Jack Marchetti]

The reason your query might not be working is you may be looking in the wrong database. Also you want to search where name like '%tablename%'

[navig8tr]

As mentioned, I tried select * from sys.tables where name like '%tablename%' (although I typed it incorrectly in my previous comment). I wouldn't mind doing this for each dB but it doesnt seem to be giving the expected results. In fact, it's not returning any results even when I search for a table I know is inside the database. I must be doing something wrong, but I'm not sure what. Thanks for the suggestions everyone

[navig8tr]

Although I did try this statement before, I wasn't doing it corrctly. I was trying to query the all the databases at the same time, but with this statement you can only search one dB at a time. In Microsoft SQL Server Mgmt Studio I just right clicked on the the dB I wanted to search and then used the statement in the query window.

[navig8tr]

Also, the link does give a method of searching multiple dBs at the same time. However, I just queried each of our approx 10 dBs manually.