How can I display the header of columns of all tables within a SCHEMA

Question

I would like to list out all the tables and column headers within a SCHEMA to be displayed in Rows within MySQL, I can't seem to find an answer online and would greatly appreciate any help.

e.g I have 50 tables within my SCHEMA, table1, table2 and table3 ....

The number of columns within the tables are also different.

What query should I use to display the following


| table1 | 01col1 header | 01col2 header | 01col3 header | 01col4 header |

| table2 | 02col1 header | 02col2 header | 02col3 header | 02col4 header | 02col5 header |

| table3 | 03col1 header | 03col2 header | 03col3 header |

| ...... |

| table50| 50col1 header | 50col2 header |

Thank you!

score 0 · Accepted Answer · answered Dec 10 '19 at 09:15

You could use information_schema.columns together with conditional aggregation.

select table_name,
        max(case when ordinal_position = 1 then column_name end) as col1name,
        max(case when ordinal_position = 2 then column_name end) as col2name,
        max(case when ordinal_position = 3 then column_name end) as col3name
from information_schema.`COLUMNS`
group by table_name;

If the max number of columns over all the tables is annoyingly large or subject to change then you could adapt this code to build a prepared statement.

score 0 · Answer 2 · answered Apr 24 '22 at 22:57

I happened to be noodling around with this very topic a month or two ago, so here's a really simple proof of concept that I wrote. It basically pretty prints first your tables and then all the columns of your tables, in HTML.

<HTML>
<HEAD>
<TITLE>List Schema</TITLE>
</HEAD>
<BODY>
<?php
# Note, untested sample code NO WARRANTY expressed or implied.
# If this causes your computer to spontaneously initiate a complete
# matter-to-energy conversion and destroy your home state, it's 
# NOT MY FAULT.

use Doctrine\Common\ClassLoader;
echo "Starting...<BR>\n";
require 'vendor/autoload.php';

$config = new \Doctrine\DBAL\Configuration();

# Note, when I created my testing database in dreamhost,
# for some reason it defaulted to utf8mb3
# This resulted in a cryptic error, which I'm afraid I didn't save.
# The fix was to alter my test database to utf8mb4 with this command:
# ALTER DATABASE databasename CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
$connectionParams = array(
                          'dbname' => 'INFORMATION_SCHEMA',
                          'user' => 'username',
                          'password' => 'password',
                          'host' => 'hostname.example..com',
                          'port' => 3306,
                          'charset' => 'utf8',
                          'driver' => 'pdo_mysql',
                          );
$dbh = \Doctrine\DBAL\DriverManager::getConnection($connectionParams, $config);
echo "Querying table.<BR>\n";
$sth = $dbh->query("SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA LIKE 'databasename'");
echo "Iterating through tables:<BR>\n";
echo "<TABLE BORDER=1>\n";
$headers = 0;
while (($row = $sth->fetchAssociative()) !== false) {
#  echo(var_dump($row));
  if (!$headers) {
    echo "<TR>";
    foreach ($row as $key => $value) {
      echo "<TH>$key</TH>";
    }
    echo "</TR>\n" ;
    $headers = 1;
  }
  echo "<TR>";
  foreach ($row as $key => $value) {
    echo "<TD>$value</TD>";
  }
  echo "</TR>\n" ;
}
echo "</TABLE>" ;
echo "<HR>\n";
$sth = $dbh->query("SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA LIKE 'databasename'");
echo "Iterating through columns:<BR>\n";
echo "<TABLE BORDER=1>\n";
$headers = 0;
while (($row = $sth->fetchAssociative()) !== false) {
#  echo(var_dump($row));
  if (!$headers) {
    echo "<TR>";
    foreach ($row as $key => $value) {
      echo "<TH>$key</TH>";
    }
    echo "</TR>\n" ;
    $headers = 1;
  }
  echo "<TR>";
  foreach ($row as $key => $value) {
    echo "<TD>$value</TD>";
  }
  echo "</TR>\n" ;
}
echo "</TABLE>" ;
echo "<HR>\n";
# Columns we care about are:
# TABLE_NAME
# COLUMN_NAME
# DATA_TYPE or COLUMN_TYPE
# CHARACTER_MAXIMUM_LENGTH
# IS_NULLABLE maybe
# COLUMN_DEFAULT
# COLUMN_KEY is PRI, UNI, or MUL
# re: COLUMN_KEY, see https://stackoverflow.com/questions/5317889/sql-keys-mul-vs-pri-vs-uni
# "a foreign key that references another table's primary key is MUL" (but it sounds like not always...)
# and https://stackoverflow.com/questions/5317889/sql-keys-mul-vs-pri-vs-uni/25101478#25101478
$sth = $dbh->query("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA LIKE 'databasename'");
while (($tablename = $sth->fetchOne()) !== false) {
  $sth_cols = $dbh->query("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA LIKE 'databasename'");
while (($tablename = $sth->fetchOne()) !== false) {
}
?>
Done!
</BODY>
</HTML>

How can I display the header of columns of all tables within a SCHEMA

2 Answers2