{"id":26,"date":"2012-02-25T08:00:15","date_gmt":"2012-02-25T08:00:15","guid":{"rendered":"http:\/\/en.latindevelopers.com\/ivancp\/?p=26"},"modified":"2012-02-25T13:16:24","modified_gmt":"2012-02-25T13:16:24","slug":"a-better-show-table-status","status":"publish","type":"post","link":"http:\/\/en.latindevelopers.com\/ivancp\/2012\/a-better-show-table-status\/","title":{"rendered":"A better SHOW TABLE STATUS"},"content":{"rendered":"

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<\/code> command is every useful, or not?.<\/p>\n

This is what we get when run show table status<\/code> in a standard 80×25 terminal screen:<\/p>\n

\"\"<\/a><\/p>\n

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:<\/p>\n

call tools.sp_status(database());\r\n+----------------------------+--------+-------+---------+-----------------+\r\n| Table Name                 | Engine | Rows  | Size    | Collation       |\r\n+----------------------------+--------+-------+---------+-----------------+\r\n| actor                      | InnoDB | 200   | 0.03 Mb | utf8_general_ci |\r\n| actor_info                 | [VIEW] | -     | -       | -               |\r\n| address                    | InnoDB | 589   | 0.09 Mb | utf8_general_ci |\r\n| category                   | InnoDB | 16    | 0.02 Mb | utf8_general_ci |\r\n| city                       | InnoDB | 427   | 0.06 Mb | utf8_general_ci |\r\n| country                    | InnoDB | 109   | 0.02 Mb | utf8_general_ci |\r\n| customer                   | InnoDB | 541   | 0.12 Mb | utf8_general_ci |\r\n| customer_list              | [VIEW] | -     | -       | -               |\r\n| film                       | InnoDB | 1131  | 0.27 Mb | utf8_general_ci |\r\n| film_actor                 | InnoDB | 5143  | 0.27 Mb | utf8_general_ci |\r\n| film_category              | InnoDB | 316   | 0.08 Mb | utf8_general_ci |\r\n| film_list                  | [VIEW] | -     | -       | -               |\r\n| film_text                  | MyISAM | 1000  | 0.31 Mb | utf8_general_ci |\r\n| inventory                  | InnoDB | 4673  | 0.36 Mb | utf8_general_ci |\r\n| language                   | InnoDB | 6     | 0.02 Mb | utf8_general_ci |\r\n| nicer_but_slower_film_list | [VIEW] | -     | -       | -               |\r\n| payment                    | InnoDB | 15422 | 2.12 Mb | utf8_general_ci |\r\n| rental                     | InnoDB | 15609 | 2.72 Mb | utf8_general_ci |\r\n| sales_by_film_category     | [VIEW] | -     | -       | -               |\r\n| sales_by_store             | [VIEW] | -     | -       | -               |\r\n| staff                      | InnoDB | 1     | 0.09 Mb | utf8_general_ci |\r\n| staff_list                 | [VIEW] | -     | -       | -               |\r\n| store                      | InnoDB | 2     | 0.05 Mb | utf8_general_ci |\r\n+----------------------------+--------+-------+---------+-----------------+\r\n23 rows in set (0.04 sec)\r\n\r\n+----------------------------+-----------+---------------------+\r\n| Routine Name               | Type      | Comment             |\r\n+----------------------------+-----------+---------------------+\r\n| get_customer_balance       | FUNCTION  |                     |\r\n| inventory_held_by_customer | FUNCTION  |                     |\r\n| inventory_in_stock         | FUNCTION  |                     |\r\n| film_in_stock              | PROCEDURE |                     |\r\n| film_not_in_stock          | PROCEDURE |                     |\r\n| rewards_report             | PROCEDURE |                     |\r\n| customer_create_date       | TRIGGER   | On INSERT: customer |\r\n| del_film                   | TRIGGER   | On DELETE: film     |\r\n| ins_film                   | TRIGGER   | On INSERT: film     |\r\n| payment_date               | TRIGGER   | On INSERT: payment  |\r\n| rental_date                | TRIGGER   | On INSERT: rental   |\r\n| upd_film                   | TRIGGER   | On UPDATE: film     |\r\n+----------------------------+-----------+---------------------+\r\n12 rows in set (0.04 sec)\r\n\r\nQuery OK, 0 rows affected (0.04 sec)<\/pre>\n
There is the procedure source code:<\/p>\n
\n
DELIMITER $$\r\nDROP PROCEDURE IF EXISTS `tools`.`sp_status` $$\r\nCREATE PROCEDURE `tools`.`sp_status`(dbname varchar(50))\r\nBEGIN \r\n-- Obtaining tables and views\r\n(\r\n    SELECT \r\n     TABLE_NAME as `Table Name`, \r\n     ENGINE as `Engine`,\r\n     TABLE_ROWS as `Rows`,\r\n     CONCAT(\r\n        (FORMAT((DATA_LENGTH + INDEX_LENGTH) \/ POWER(1024,2),2))\r\n        , ' Mb')\r\n       as `Size`,\r\n     TABLE_COLLATION as `Collation`\r\n    FROM information_schema.TABLES\r\n    WHERE TABLES.TABLE_SCHEMA = dbname\r\n          AND TABLES.TABLE_TYPE = 'BASE TABLE'\r\n)\r\nUNION\r\n(\r\n    SELECT \r\n     TABLE_NAME as `Table Name`, \r\n     '[VIEW]' as `Engine`,\r\n     '-' as `Rows`,\r\n     '-' `Size`,\r\n     '-' as `Collation`\r\n    FROM information_schema.TABLES\r\n    WHERE TABLES.TABLE_SCHEMA = dbname \r\n          AND TABLES.TABLE_TYPE = 'VIEW'\r\n)\r\nORDER BY 1;\r\n-- Obtaining functions, procedures and triggers\r\n(\r\n    SELECT ROUTINE_NAME as `Routine Name`, \r\n     ROUTINE_TYPE as `Type`,\r\n     '' as `Comment`\r\n    FROM information_schema.ROUTINES\r\n    WHERE ROUTINE_SCHEMA = dbname\r\n    ORDER BY ROUTINES.ROUTINE_TYPE, ROUTINES.ROUTINE_NAME\r\n)\r\nUNION\r\n(\r\n    SELECT TRIGGER_NAME,'TRIGGER' as `Type`, \r\n    concat('On ',EVENT_MANIPULATION,': ',EVENT_OBJECT_TABLE) as `Comment`\r\n    FROM information_schema.TRIGGERS\r\n    WHERE EVENT_OBJECT_SCHEMA = dbname\r\n)\r\nORDER BY 2,1;\r\nEND$$\r\nDELIMITER ;<\/pre>\n
To use in your place you must call as:<\/p>\n
mysql> call tools.sp_status(database());<\/pre>\n
Note the stored procedure has created in tools<\/code> 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<\/strong> obtain the current database from inside of stored procedure.<\/p>\n
I published that code on\u00a0 MySQL custom show table status<\/a> entry at forge.mysql.com.<\/p>\n
Any ideas to improving the source code to make it more useful?<\/p>\n
 <\/p>\n
\n
`<\/pre>\n<\/div>\n
Share this:<\/h3>