CODEWITHSUNDEEP
mysqlsqldatabasephpmyadmin
nshah143
nshah143

Reputation: 559

mysql query to search a table name from multiple databases

I am facing a problem in searching a particular table_name. I have around 50 databases on the server and i wish to search a table_name say X is created in which all databases. Is there any straight-forward to find the exact databases in which X table is found in MYSQL through phpMyAdmin.

Any help will be appreciated. Thanks

Upvotes: 0

Views: 2889

Answers (2)

Jineesh Uvantavida
Jineesh Uvantavida

Reputation: 661

Please query the information_schema.TABLES with table_type='BASE TABLE' and providing the name of the table(table_name).

This query will give us all the tables with '<your_table_name>' in all the databases that are currently in our server.

Please change '<your_table_name>' as per your requirement.

 SELECT TABLE_SCHEMA, TABLE_NAME
    FROM `information_schema`.`TABLES` 
    where table_type='BASE TABLE'
    and table_name =  '<your_table_name>'
    limit 100;

Upvotes: 0

Aruna Tebel
Aruna Tebel

Reputation: 1466

You can query the information_schema database for this. The below query will return the names of all the databases, which contains the table your_table_name.

SELECT `TABLE_SCHEMA` 
FROM `information_schema`.`TABLES` 
WHERE `TABLE_NAME` = 'your_table_name'

I hope this is what you are looking for.

According to the MySQL documentation about information_schema database,

INFORMATION_SCHEMA provides access to database metadata.

Metadata is data about the data, such as the name of a database or table, the data type of a column, or access privileges. Other terms that sometimes are used for this information are data dictionary and system catalog.

Upvotes: 1

Related Questions