A better SHOW TABLE STATUS

Posted on February 25, 2012 Comments

From command line we have the entire MySQL server on hands (if we have privileges too of course) but we don’t have a overall overview, at this point the show table status command is every useful, or not?.

This is what we get when run show table status in a standard 80×25 terminal screen:

We can maximize the terminal window and decrease font size, but not all the time we need that lots of info. Some time ago I develop a stored procedure to get a global overview including functions and stored procedures. The result is pretty comprehensible:

call tools.sp_status(database());
+----------------------------+--------+-------+---------+-----------------+
| Table Name                 | Engine | Rows  | Size    | Collation       |
+----------------------------+--------+-------+---------+-----------------+
| actor                      | InnoDB | 200   | 0.03 Mb | utf8_general_ci |
| actor_info                 | [VIEW] | -     | -       | -               |
| address                    | InnoDB | 589   | 0.09 Mb | utf8_general_ci |
| category                   | InnoDB | 16    | 0.02 Mb | utf8_general_ci |
| city                       | InnoDB | 427   | 0.06 Mb | utf8_general_ci |
| country                    | InnoDB | 109   | 0.02 Mb | utf8_general_ci |
| customer                   | InnoDB | 541   | 0.12 Mb | utf8_general_ci |
| customer_list              | [VIEW] | -     | -       | -               |
| film                       | InnoDB | 1131  | 0.27 Mb | utf8_general_ci |
| film_actor                 | InnoDB | 5143  | 0.27 Mb | utf8_general_ci |
| film_category              | InnoDB | 316   | 0.08 Mb | utf8_general_ci |
| film_list                  | [VIEW] | -     | -       | -               |
| film_text                  | MyISAM | 1000  | 0.31 Mb | utf8_general_ci |
| inventory                  | InnoDB | 4673  | 0.36 Mb | utf8_general_ci |
| language                   | InnoDB | 6     | 0.02 Mb | utf8_general_ci |
| nicer_but_slower_film_list | [VIEW] | -     | -       | -               |
| payment                    | InnoDB | 15422 | 2.12 Mb | utf8_general_ci |
| rental                     | InnoDB | 15609 | 2.72 Mb | utf8_general_ci |
| sales_by_film_category     | [VIEW] | -     | -       | -               |
| sales_by_store             | [VIEW] | -     | -       | -               |
| staff                      | InnoDB | 1     | 0.09 Mb | utf8_general_ci |
| staff_list                 | [VIEW] | -     | -       | -               |
| store                      | InnoDB | 2     | 0.05 Mb | utf8_general_ci |
+----------------------------+--------+-------+---------+-----------------+
23 rows in set (0.04 sec)
 
+----------------------------+-----------+---------------------+
| Routine Name               | Type      | Comment             |
+----------------------------+-----------+---------------------+
| get_customer_balance       | FUNCTION  |                     |
| inventory_held_by_customer | FUNCTION  |                     |
| inventory_in_stock         | FUNCTION  |                     |
| film_in_stock              | PROCEDURE |                     |
| film_not_in_stock          | PROCEDURE |                     |
| rewards_report             | PROCEDURE |                     |
| customer_create_date       | TRIGGER   | On INSERT: customer |
| del_film                   | TRIGGER   | On DELETE: film     |
| ins_film                   | TRIGGER   | On INSERT: film     |
| payment_date               | TRIGGER   | On INSERT: payment  |
| rental_date                | TRIGGER   | On INSERT: rental   |
| upd_film                   | TRIGGER   | On UPDATE: film     |
+----------------------------+-----------+---------------------+
12 rows in set (0.04 sec)
 
Query OK, 0 rows affected (0.04 sec)

There is the procedure source code:

DELIMITER $$
DROP PROCEDURE IF EXISTS `tools`.`sp_status` $$
CREATE PROCEDURE `tools`.`sp_status`(dbname VARCHAR(50))
BEGIN 
-- Obtaining tables and views
(
    SELECT 
     TABLE_NAME AS `Table Name`, 
     ENGINE AS `Engine`,
     TABLE_ROWS AS `Rows`,
     CONCAT(
        (FORMAT((DATA_LENGTH + INDEX_LENGTH) / POWER(1024,2),2))
        , ' Mb')
       AS `Size`,
     TABLE_COLLATION AS `Collation`
    FROM information_schema.TABLES
    WHERE TABLES.TABLE_SCHEMA = dbname
          AND TABLES.TABLE_TYPE = 'BASE TABLE'
)
UNION
(
    SELECT 
     TABLE_NAME AS `Table Name`, 
     '[VIEW]' AS `Engine`,
     '-' AS `Rows`,
     '-' `Size`,
     '-' AS `Collation`
    FROM information_schema.TABLES
    WHERE TABLES.TABLE_SCHEMA = dbname 
          AND TABLES.TABLE_TYPE = 'VIEW'
)
ORDER BY 1;
-- Obtaining functions, procedures and triggers
(
    SELECT ROUTINE_NAME AS `Routine Name`, 
     ROUTINE_TYPE AS `Type`,
     '' AS `Comment`
    FROM information_schema.ROUTINES
    WHERE ROUTINE_SCHEMA = dbname
    ORDER BY ROUTINES.ROUTINE_TYPE, ROUTINES.ROUTINE_NAME
)
UNION
(
    SELECT TRIGGER_NAME,'TRIGGER' AS `Type`, 
    concat('On ',EVENT_MANIPULATION,': ',EVENT_OBJECT_TABLE) AS `Comment`
    FROM information_schema.TRIGGERS
    WHERE EVENT_OBJECT_SCHEMA = dbname
)
ORDER BY 2,1;
END$$
DELIMITER ;

To use in your place you must call as:

mysql> call tools.sp_status(database());

Note the stored procedure has created in tools database (you can use another db), the goal of this is to call that useful procedure from any database, and it receives the name of database as parameter because is not possible obtain the current database from inside of stored procedure.

I published that code on  MySQL custom show table status entry at forge.mysql.com.

Any ideas to improving the source code to make it more useful?

 

`

Related Posts :

Comments

  1. Cool.
    A shortcut to display large table,use ‘\P less -SN’, use ‘nopager’ to disable it.

  2. Its fantastic as your other posts : D, regards for putting up. “The real hero is always a hero by mistake he dreams of being an honest coward like everybody else.” by Umberto Eco.

  3. Great job, I was actually looking for something to see how much free space I had in my .ibd files so I tweaked your procedure and added DATA_FREE and DATA_LENGTH + INDEX_LENGTH + DATA_FREE as well.
    Thanks for this.

  4. I got this error message.

    ERROR 1267 (HY000): Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation ‘=’

Pings

  1. MySQL の SHOW TABLE STATUS を見やすくする方法 | Carpe Diem
  2. 2 petits outils pratiques pour MySQL « L'Endormitoire

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre lang="" line="" escaped="" cssfile="">