CodeIgniter Working with multiple Databases

Question

Hello,

I am trying to list all the mysql databases and their respective tables, I am currently using this, but can anybody recommend if there is any better way.

$q = $this->db->query('SHOW DATABASES');
$databases = $q->result_array();

foreach($databases as $db) {
  $this->db->query('USE '. $db['Database']);

  $q = $this->db->query('SHOW TABLES');
  $tables = $q->result_array();             
}

Answer 1

You can use the information_schema special database, which has tables that describe all the other databases, tables, and columns.

That way you only need 1 query:

SELECT table_schema, table_name
FROM information_schema.tables
ORDER BY table_schema, table_name;