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 | +-------------+--------+---------+-------------------+------+
Last updated 22 Nov 2009
