SQL: How to display the tables of a database

The SQL command DESCRIBE puts in an array the name of columns of a table with the types and other parameters of the column. It is used in conjunction with SHOW TABLES which returns the list of tables.

We can with an algorithm show all the columns and see the structure of the database, with the format of the fields.

The configuration file is taken as an example from Wordpress:

<?php
define('DB_NAME', 'theme');
define('DB_USER', 'root');
define('DB_PASSWORD', '');
define('DB_HOST', 'localhost');
define('DB_CHARSET', 'utf8');
define('DB_COLLATE', '');
$table_prefix  = 'WP_';
?>

We includes into this file the connection to the database:

$base = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD)
or die("MySQL database '".DB_NAME."' not accessible.<br>\n");  

mysqli_select_db($base, DB_NAME)
or die("Enable to select ".DB_NAME." database<br>\n");

SHOW TABLE

in a first step, we get the list of tables:

$results = mysqli_query($base, "SHOW TABLES");
if($results == false)   die("Empty database");

$tables = array();
while($arr = mysqli_fetch_array($results))
{
  array_push($tables, $arr['0']);
}

The table names are stored into the $tables array.

DESCRIBE

For each table, we can retrieve the column names, type, if it is allowed to be null and the key:

foreach($tables as $table)
{
  $results = mysqli_query($base, "DESCRIBE $table");

  echo "TABLE $table <br>";

  while($arr = mysqli_fetch_array($results))
  {
    echo "Name ", $arr['0'], "<br>";
    echo "Type ", $arr['1'], "<br>";
    echo "May be null ", $arr['2'], "<br>";
    echo "Key ", $arr['3'], "<br>";
  }
}

"PRI" is a shortcut to PRIMARY KEY.

For a more readable view, we use a table in the complete file.

See the complete code:

• Using SQL commands SHOW and DESCRIBE.