SQL statement to find a table by its name

sql sql-server database find tablename
navig8tr picture navig8tr · Jun 2, 2014 · Viewed 20.1k times · Source

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?

Answer

Jack Marchetti picture Jack Marchetti · Jun 2, 2014

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.

Related questions

Reset identity seed after deleting records in SQL Server

I have inserted records into a SQL Server database table. The table had a primary key defined and the auto increment identity seed is set to “Yes”. This is done primarily because in SQL Azure, each table has to have …

Read More
How do I list all the columns in a table?

For the various popular database systems, how do you list all the columns in a table?

Read More
How to replace a string in a SQL Server Table Column

I have a table (SQL Sever) which references paths (UNC or otherwise), but now the path is going to change. In the path column, I have many records and I need to change just a portion of the path, but …

Read More