Show Tables

from the Artful Common Queries page


The MySQL SHOW TABLES command is fine, but sometimes we want a little more information.

This simple stored procedure lists the table name, engine type, version, collation and rowcount for every table in a database. (Individual databases come and go, so we keep all such database-wide stored routines in a system database.)

DROP PROCEDURE IF EXISTS showtables;
CREATE PROCEDURE showtables()
  SELECT
    table_name AS 'Table',
    IFNULL(engine, 'VIEW') AS Engine,
    version AS Version,
    table_collation AS Collation,
    table_rows AS Rows
  FROM information_schema.tables
  WHERE table_schema=DATABASE();

If you often want to list tables in a DB not currently USEd, here is a parameter-driven version:

DROP PROCEDURE IF EXISTS showtablesDB;
CREATE PROCEDURE showtablesDB( IN dbname CHAR(64) )
  SELECT
    table_name AS 'Table',
    IFNULL(engine, 'VIEW') AS Engine,
    version AS Version,
    table_collation AS Collation,
    table_rows AS Rows
  FROM information_schema.tables
  WHERE table_schema=dbname;

For a sys database of ours, this shows ...

+-------------+--------+---------+-------------------+------+
| Table       | Engine | Version | Collation         | Rows |
+-------------+--------+---------+-------------------+------+
| dbsize      | VIEW   |    NULL | NULL              | NULL |
| dcal        | MEMORY |      10 | latin1_swedish_ci |    0 |
| digits      | MyISAM |      10 | latin1_swedish_ci |   10 |
| errors      | MEMORY |      10 | latin1_swedish_ci |    0 |
| ints        | MyISAM |      10 | latin1_swedish_ci |   10 |
| results     | MEMORY |      10 | latin1_swedish_ci |    0 |
| theusualfks | MyISAM |      10 | latin1_swedish_ci |    5 |
| viewparams  | MyISAM |      10 | latin1_swedish_ci |    2 |
+-------------+--------+---------+-------------------+------+


Return to the Artful Common Queries page