CODEWITHSUNDEEP
phpmysql
Eugene
Eugene

Reputation: 4389

A way to search form table in MySQL database

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;

Upvotes: 1

Views: 251

Answers (4)

Lizard
Lizard

Reputation: 44992

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 framework would. If the table name differs, then the coder would specify tghe table name in the model.

Upvotes: 0

Martijn Engler
Martijn Engler

Reputation: 106

SHOW TABLES LIKE '%settings';

Upvotes: 1

Raja
Raja

Reputation: 139

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

Upvotes: 1

Liutas
Liutas

Reputation: 5783

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%')

Upvotes: 0

Related Questions