Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

list all tables in a database with MySQLi

I have looked around and still can't find how to list all my tables in a database. is it possible with MySQLi?

Thanks.

like image 529
Run Avatar asked Jan 16 '11 00:01

Run


People also ask

How do I list all tables in DB?

All Database Tables If you want to list all tables in the Oracle database, you can query the dba_tables view. SELECT table_name FROM dba_tables ORDER BY table_name ASC; This view (and all others starting with dba_) are meant for database administrators.

How do I list all the tables in a MySQL database?

To get a list of the tables in a MySQL database, use the mysql client tool to connect to the MySQL server and run the SHOW TABLES command. The optional FULL modifier will show the table type as a second output column.


3 Answers

There are many ways.

SHOW TABLES

Is the most simple SQL statement for doing that. You can also take a look at INFORMATION_SCHEMA.TABLES if you want to have more details or do some filtering or such.

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA LIKE 'your_database';
like image 151
johannes Avatar answered Sep 28 '22 23:09

johannes


Using PHP 5.5 or later, a simple solution is using PHP's built-in array_column() function.

$link = mysqli_connect(DBHOST, DBUSER, DBPASS, DBNAME);
$listdbtables = array_column($link->query('SHOW TABLES')->fetch_all(),0);
like image 28
Lorenz Lo Sauer Avatar answered Sep 28 '22 23:09

Lorenz Lo Sauer


I'd try something like:

function get_tables()
{
  $tableList = array();
  $res = mysqli_query($this->conn,"SHOW TABLES");
  while($cRow = mysqli_fetch_array($res))
  {
    $tableList[] = $cRow[0];
  }
  return $tableList;
}
like image 40
cubic1271 Avatar answered Sep 29 '22 23:09

cubic1271