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 |
+-------------+--------+---------+-------------------+------+
|
|