A way to search form table in MySQL database.

Question

I looked for a way to scan database for a specific table. For example i have:

Database: system_ultimate Table: system_settings

And let us say, that one doesn't know precise name of the table. He only knows, that it is some how connected to the word settings. How could he search for that table name then?

I understand, that usually people who develop know, what they develop, but I'm trying to get hang of MVC and I'm trying to stay as far away as possible from direct communication with table using the name.

I know, that to see all tables I could use SHOW TABLES;

Answer 1

The word settings would have to match a certain set of criteria or naming convention so that you could calculate the correct names, just like any MVC framwork would. If the table name differs, then the coder would specify tghe table name in the model.

Answer 2

MySQL has database information_schema in this DB Mysql holds all DB names tables names and columns names.

You can get all tables columns with select:

SELECT `TABLE_SCHEMA` , `TABLE_NAME` , `COLUMN_NAME`
FROM `information_schema`.`COLUMNS`
WHERE `TABLE_SCHEMA` = 'myDb'
AND `TABLE_NAME` LIKE ('test%')

Answer 3

You can get these from INFORMATION_SCHEMA

SELECT table_name FROM INFORMATION_SCHEMA.TABLES
  WHERE table_schema = 'system_ultimate'
 table_name LIKE '%word%'

more on this can be found on http://dev.mysql.com/doc/refman/5.0/en/tables-table.html

Answer 4

SHOW TABLES LIKE '%settings';