Edit File: is_tables_mysql.result
DROP DATABASE IF EXISTS test1; CREATE DATABASE test1; SELECT *, LEFT( table_comment, IF(INSTR(table_comment,'InnoDB free') = 0 AND INSTR(table_comment,'number_of_replicas') = 0, LENGTH(table_comment), INSTR(table_comment,'InnoDB free') + INSTR(table_comment,'number_of_replicas') - 1)) AS "user_comment", '-----------------------------------------------------' AS "Separator" FROM information_schema.tables WHERE table_schema = 'mysql' ORDER BY table_schema,table_name; TABLE_CATALOG def TABLE_SCHEMA mysql TABLE_NAME columns_priv TABLE_TYPE BASE TABLE ENGINE MyISAM VERSION 10 ROW_FORMAT Fixed TABLE_ROWS #TBLR# AVG_ROW_LENGTH #ARL# DATA_LENGTH #DL# MAX_DATA_LENGTH #MDL# INDEX_LENGTH #IL# DATA_FREE #DF# AUTO_INCREMENT NULL CREATE_TIME #CRT# UPDATE_TIME #UT# CHECK_TIME #CT# TABLE_COLLATION utf8_bin CHECKSUM NULL CREATE_OPTIONS #CO# TABLE_COMMENT #TC# user_comment Column privileges Separator ----------------------------------------------------- TABLE_CATALOG def TABLE_SCHEMA mysql TABLE_NAME db TABLE_TYPE BASE TABLE ENGINE MyISAM VERSION 10 ROW_FORMAT Fixed TABLE_ROWS #TBLR# AVG_ROW_LENGTH #ARL# DATA_LENGTH #DL# MAX_DATA_LENGTH #MDL# INDEX_LENGTH #IL# DATA_FREE #DF# AUTO_INCREMENT NULL CREATE_TIME #CRT# UPDATE_TIME #UT# CHECK_TIME #CT# TABLE_COLLATION utf8_bin CHECKSUM NULL CREATE_OPTIONS #CO# TABLE_COMMENT #TC# user_comment Database privileges Separator ----------------------------------------------------- TABLE_CATALOG def TABLE_SCHEMA mysql TABLE_NAME event TABLE_TYPE BASE TABLE ENGINE MyISAM VERSION 10 ROW_FORMAT Dynamic TABLE_ROWS #TBLR# AVG_ROW_LENGTH #ARL# DATA_LENGTH #DL# MAX_DATA_LENGTH #MDL# INDEX_LENGTH #IL# DATA_FREE #DF# AUTO_INCREMENT NULL CREATE_TIME #CRT# UPDATE_TIME #UT# CHECK_TIME #CT# TABLE_COLLATION utf8_general_ci CHECKSUM NULL CREATE_OPTIONS #CO# TABLE_COMMENT #TC# user_comment Events Separator ----------------------------------------------------- TABLE_CATALOG def TABLE_SCHEMA mysql TABLE_NAME func TABLE_TYPE BASE TABLE ENGINE MyISAM VERSION 10 ROW_FORMAT Fixed TABLE_ROWS #TBLR# AVG_ROW_LENGTH #ARL# DATA_LENGTH #DL# MAX_DATA_LENGTH #MDL# INDEX_LENGTH #IL# DATA_FREE #DF# AUTO_INCREMENT NULL CREATE_TIME #CRT# UPDATE_TIME #UT# CHECK_TIME #CT# TABLE_COLLATION utf8_bin CHECKSUM NULL CREATE_OPTIONS #CO# TABLE_COMMENT #TC# user_comment User defined functions Separator ----------------------------------------------------- TABLE_CATALOG def TABLE_SCHEMA mysql TABLE_NAME general_log TABLE_TYPE BASE TABLE ENGINE CSV VERSION 10 ROW_FORMAT Dynamic TABLE_ROWS #TBLR# AVG_ROW_LENGTH #ARL# DATA_LENGTH #DL# MAX_DATA_LENGTH #MDL# INDEX_LENGTH #IL# DATA_FREE #DF# AUTO_INCREMENT NULL CREATE_TIME #CRT# UPDATE_TIME #UT# CHECK_TIME #CT# TABLE_COLLATION utf8_general_ci CHECKSUM NULL CREATE_OPTIONS #CO# TABLE_COMMENT #TC# user_comment General log Separator ----------------------------------------------------- TABLE_CATALOG def TABLE_SCHEMA mysql TABLE_NAME help_category TABLE_TYPE BASE TABLE ENGINE MyISAM VERSION 10 ROW_FORMAT Dynamic TABLE_ROWS #TBLR# AVG_ROW_LENGTH #ARL# DATA_LENGTH #DL# MAX_DATA_LENGTH #MDL# INDEX_LENGTH #IL# DATA_FREE #DF# AUTO_INCREMENT NULL CREATE_TIME #CRT# UPDATE_TIME #UT# CHECK_TIME #CT# TABLE_COLLATION utf8_general_ci CHECKSUM NULL CREATE_OPTIONS #CO# TABLE_COMMENT #TC# user_comment help categories Separator ----------------------------------------------------- TABLE_CATALOG def TABLE_SCHEMA mysql TABLE_NAME help_keyword TABLE_TYPE BASE TABLE ENGINE MyISAM VERSION 10 ROW_FORMAT Fixed TABLE_ROWS #TBLR# AVG_ROW_LENGTH #ARL# DATA_LENGTH #DL# MAX_DATA_LENGTH #MDL# INDEX_LENGTH #IL# DATA_FREE #DF# AUTO_INCREMENT NULL CREATE_TIME #CRT# UPDATE_TIME #UT# CHECK_TIME #CT# TABLE_COLLATION utf8_general_ci CHECKSUM NULL CREATE_OPTIONS #CO# TABLE_COMMENT #TC# user_comment help keywords Separator ----------------------------------------------------- TABLE_CATALOG def TABLE_SCHEMA mysql TABLE_NAME help_relation TABLE_TYPE BASE TABLE ENGINE MyISAM VERSION 10 ROW_FORMAT Fixed TABLE_ROWS #TBLR# AVG_ROW_LENGTH #ARL# DATA_LENGTH #DL# MAX_DATA_LENGTH #MDL# INDEX_LENGTH #IL# DATA_FREE #DF# AUTO_INCREMENT NULL CREATE_TIME #CRT# UPDATE_TIME #UT# CHECK_TIME #CT# TABLE_COLLATION utf8_general_ci CHECKSUM NULL CREATE_OPTIONS #CO# TABLE_COMMENT #TC# user_comment keyword-topic relation Separator ----------------------------------------------------- TABLE_CATALOG def TABLE_SCHEMA mysql TABLE_NAME help_topic TABLE_TYPE BASE TABLE ENGINE MyISAM VERSION 10 ROW_FORMAT Dynamic TABLE_ROWS #TBLR# AVG_ROW_LENGTH #ARL# DATA_LENGTH #DL# MAX_DATA_LENGTH #MDL# INDEX_LENGTH #IL# DATA_FREE #DF# AUTO_INCREMENT NULL CREATE_TIME #CRT# UPDATE_TIME #UT# CHECK_TIME #CT# TABLE_COLLATION utf8_general_ci CHECKSUM NULL CREATE_OPTIONS #CO# TABLE_COMMENT #TC# user_comment help topics Separator ----------------------------------------------------- TABLE_CATALOG def TABLE_SCHEMA mysql TABLE_NAME innodb_index_stats TABLE_TYPE BASE TABLE ENGINE InnoDB VERSION 10 ROW_FORMAT Compact TABLE_ROWS #TBLR# AVG_ROW_LENGTH #ARL# DATA_LENGTH #DL# MAX_DATA_LENGTH #MDL# INDEX_LENGTH #IL# DATA_FREE #DF# AUTO_INCREMENT NULL CREATE_TIME #CRT# UPDATE_TIME #UT# CHECK_TIME #CT# TABLE_COLLATION utf8_bin CHECKSUM NULL CREATE_OPTIONS #CO# TABLE_COMMENT #TC# user_comment Separator ----------------------------------------------------- TABLE_CATALOG def TABLE_SCHEMA mysql TABLE_NAME innodb_table_stats TABLE_TYPE BASE TABLE ENGINE InnoDB VERSION 10 ROW_FORMAT Compact TABLE_ROWS #TBLR# AVG_ROW_LENGTH #ARL# DATA_LENGTH #DL# MAX_DATA_LENGTH #MDL# INDEX_LENGTH #IL# DATA_FREE #DF# AUTO_INCREMENT NULL CREATE_TIME #CRT# UPDATE_TIME #UT# CHECK_TIME #CT# TABLE_COLLATION utf8_bin CHECKSUM NULL CREATE_OPTIONS #CO# TABLE_COMMENT #TC# user_comment Separator ----------------------------------------------------- TABLE_CATALOG def TABLE_SCHEMA mysql TABLE_NAME ndb_binlog_index TABLE_TYPE BASE TABLE ENGINE MyISAM VERSION 10 ROW_FORMAT Dynamic TABLE_ROWS #TBLR# AVG_ROW_LENGTH #ARL# DATA_LENGTH #DL# MAX_DATA_LENGTH #MDL# INDEX_LENGTH #IL# DATA_FREE #DF# AUTO_INCREMENT NULL CREATE_TIME #CRT# UPDATE_TIME #UT# CHECK_TIME #CT# TABLE_COLLATION latin1_swedish_ci CHECKSUM NULL CREATE_OPTIONS #CO# TABLE_COMMENT #TC# user_comment Separator ----------------------------------------------------- TABLE_CATALOG def TABLE_SCHEMA mysql TABLE_NAME plugin TABLE_TYPE BASE TABLE ENGINE MyISAM VERSION 10 ROW_FORMAT Dynamic TABLE_ROWS #TBLR# AVG_ROW_LENGTH #ARL# DATA_LENGTH #DL# MAX_DATA_LENGTH #MDL# INDEX_LENGTH #IL# DATA_FREE #DF# AUTO_INCREMENT NULL CREATE_TIME #CRT# UPDATE_TIME #UT# CHECK_TIME #CT# TABLE_COLLATION utf8_general_ci CHECKSUM NULL CREATE_OPTIONS #CO# TABLE_COMMENT #TC# user_comment MySQL plugins Separator ----------------------------------------------------- TABLE_CATALOG def TABLE_SCHEMA mysql TABLE_NAME proc TABLE_TYPE BASE TABLE ENGINE MyISAM VERSION 10 ROW_FORMAT Dynamic TABLE_ROWS #TBLR# AVG_ROW_LENGTH #ARL# DATA_LENGTH #DL# MAX_DATA_LENGTH #MDL# INDEX_LENGTH #IL# DATA_FREE #DF# AUTO_INCREMENT NULL CREATE_TIME #CRT# UPDATE_TIME #UT# CHECK_TIME #CT# TABLE_COLLATION utf8_general_ci CHECKSUM NULL CREATE_OPTIONS #CO# TABLE_COMMENT #TC# user_comment Stored Procedures Separator ----------------------------------------------------- TABLE_CATALOG def TABLE_SCHEMA mysql TABLE_NAME procs_priv TABLE_TYPE BASE TABLE ENGINE MyISAM VERSION 10 ROW_FORMAT Fixed TABLE_ROWS #TBLR# AVG_ROW_LENGTH #ARL# DATA_LENGTH #DL# MAX_DATA_LENGTH #MDL# INDEX_LENGTH #IL# DATA_FREE #DF# AUTO_INCREMENT NULL CREATE_TIME #CRT# UPDATE_TIME #UT# CHECK_TIME #CT# TABLE_COLLATION utf8_bin CHECKSUM NULL CREATE_OPTIONS #CO# TABLE_COMMENT #TC# user_comment Procedure privileges Separator ----------------------------------------------------- TABLE_CATALOG def TABLE_SCHEMA mysql TABLE_NAME proxies_priv TABLE_TYPE BASE TABLE ENGINE MyISAM VERSION 10 ROW_FORMAT Fixed TABLE_ROWS #TBLR# AVG_ROW_LENGTH #ARL# DATA_LENGTH #DL# MAX_DATA_LENGTH #MDL# INDEX_LENGTH #IL# DATA_FREE #DF# AUTO_INCREMENT NULL CREATE_TIME #CRT# UPDATE_TIME #UT# CHECK_TIME #CT# TABLE_COLLATION utf8_bin CHECKSUM NULL CREATE_OPTIONS #CO# TABLE_COMMENT #TC# user_comment User proxy privileges Separator ----------------------------------------------------- TABLE_CATALOG def TABLE_SCHEMA mysql TABLE_NAME servers TABLE_TYPE BASE TABLE ENGINE MyISAM VERSION 10 ROW_FORMAT Fixed TABLE_ROWS #TBLR# AVG_ROW_LENGTH #ARL# DATA_LENGTH #DL# MAX_DATA_LENGTH #MDL# INDEX_LENGTH #IL# DATA_FREE #DF# AUTO_INCREMENT NULL CREATE_TIME #CRT# UPDATE_TIME #UT# CHECK_TIME #CT# TABLE_COLLATION utf8_general_ci CHECKSUM NULL CREATE_OPTIONS #CO# TABLE_COMMENT #TC# user_comment MySQL Foreign Servers table Separator ----------------------------------------------------- TABLE_CATALOG def TABLE_SCHEMA mysql TABLE_NAME slave_master_info TABLE_TYPE BASE TABLE ENGINE InnoDB VERSION 10 ROW_FORMAT Compact TABLE_ROWS #TBLR# AVG_ROW_LENGTH #ARL# DATA_LENGTH #DL# MAX_DATA_LENGTH #MDL# INDEX_LENGTH #IL# DATA_FREE #DF# AUTO_INCREMENT NULL CREATE_TIME #CRT# UPDATE_TIME #UT# CHECK_TIME #CT# TABLE_COLLATION utf8_general_ci CHECKSUM NULL CREATE_OPTIONS #CO# TABLE_COMMENT #TC# user_comment Master Information Separator ----------------------------------------------------- TABLE_CATALOG def TABLE_SCHEMA mysql TABLE_NAME slave_relay_log_info TABLE_TYPE BASE TABLE ENGINE InnoDB VERSION 10 ROW_FORMAT Compact TABLE_ROWS #TBLR# AVG_ROW_LENGTH #ARL# DATA_LENGTH #DL# MAX_DATA_LENGTH #MDL# INDEX_LENGTH #IL# DATA_FREE #DF# AUTO_INCREMENT NULL CREATE_TIME #CRT# UPDATE_TIME #UT# CHECK_TIME #CT# TABLE_COLLATION utf8_general_ci CHECKSUM NULL CREATE_OPTIONS #CO# TABLE_COMMENT #TC# user_comment Relay Log Information Separator ----------------------------------------------------- TABLE_CATALOG def TABLE_SCHEMA mysql TABLE_NAME slave_worker_info TABLE_TYPE BASE TABLE ENGINE InnoDB VERSION 10 ROW_FORMAT Compact TABLE_ROWS #TBLR# AVG_ROW_LENGTH #ARL# DATA_LENGTH #DL# MAX_DATA_LENGTH #MDL# INDEX_LENGTH #IL# DATA_FREE #DF# AUTO_INCREMENT NULL CREATE_TIME #CRT# UPDATE_TIME #UT# CHECK_TIME #CT# TABLE_COLLATION utf8_general_ci CHECKSUM NULL CREATE_OPTIONS #CO# TABLE_COMMENT #TC# user_comment Worker Information Separator ----------------------------------------------------- TABLE_CATALOG def TABLE_SCHEMA mysql TABLE_NAME slow_log TABLE_TYPE BASE TABLE ENGINE CSV VERSION 10 ROW_FORMAT Dynamic TABLE_ROWS #TBLR# AVG_ROW_LENGTH #ARL# DATA_LENGTH #DL# MAX_DATA_LENGTH #MDL# INDEX_LENGTH #IL# DATA_FREE #DF# AUTO_INCREMENT NULL CREATE_TIME #CRT# UPDATE_TIME #UT# CHECK_TIME #CT# TABLE_COLLATION utf8_general_ci CHECKSUM NULL CREATE_OPTIONS #CO# TABLE_COMMENT #TC# user_comment Slow log Separator ----------------------------------------------------- TABLE_CATALOG def TABLE_SCHEMA mysql TABLE_NAME tables_priv TABLE_TYPE BASE TABLE ENGINE MyISAM VERSION 10 ROW_FORMAT Fixed TABLE_ROWS #TBLR# AVG_ROW_LENGTH #ARL# DATA_LENGTH #DL# MAX_DATA_LENGTH #MDL# INDEX_LENGTH #IL# DATA_FREE #DF# AUTO_INCREMENT NULL CREATE_TIME #CRT# UPDATE_TIME #UT# CHECK_TIME #CT# TABLE_COLLATION utf8_bin CHECKSUM NULL CREATE_OPTIONS #CO# TABLE_COMMENT #TC# user_comment Table privileges Separator ----------------------------------------------------- TABLE_CATALOG def TABLE_SCHEMA mysql TABLE_NAME time_zone TABLE_TYPE BASE TABLE ENGINE MyISAM VERSION 10 ROW_FORMAT Fixed TABLE_ROWS #TBLR# AVG_ROW_LENGTH #ARL# DATA_LENGTH #DL# MAX_DATA_LENGTH #MDL# INDEX_LENGTH #IL# DATA_FREE #DF# AUTO_INCREMENT 6 CREATE_TIME #CRT# UPDATE_TIME #UT# CHECK_TIME #CT# TABLE_COLLATION utf8_general_ci CHECKSUM NULL CREATE_OPTIONS #CO# TABLE_COMMENT #TC# user_comment Time zones Separator ----------------------------------------------------- TABLE_CATALOG def TABLE_SCHEMA mysql TABLE_NAME time_zone_leap_second TABLE_TYPE BASE TABLE ENGINE MyISAM VERSION 10 ROW_FORMAT Fixed TABLE_ROWS #TBLR# AVG_ROW_LENGTH #ARL# DATA_LENGTH #DL# MAX_DATA_LENGTH #MDL# INDEX_LENGTH #IL# DATA_FREE #DF# AUTO_INCREMENT NULL CREATE_TIME #CRT# UPDATE_TIME #UT# CHECK_TIME #CT# TABLE_COLLATION utf8_general_ci CHECKSUM NULL CREATE_OPTIONS #CO# TABLE_COMMENT #TC# user_comment Leap seconds information for time zones Separator ----------------------------------------------------- TABLE_CATALOG def TABLE_SCHEMA mysql TABLE_NAME time_zone_name TABLE_TYPE BASE TABLE ENGINE MyISAM VERSION 10 ROW_FORMAT Fixed TABLE_ROWS #TBLR# AVG_ROW_LENGTH #ARL# DATA_LENGTH #DL# MAX_DATA_LENGTH #MDL# INDEX_LENGTH #IL# DATA_FREE #DF# AUTO_INCREMENT NULL CREATE_TIME #CRT# UPDATE_TIME #UT# CHECK_TIME #CT# TABLE_COLLATION utf8_general_ci CHECKSUM NULL CREATE_OPTIONS #CO# TABLE_COMMENT #TC# user_comment Time zone names Separator ----------------------------------------------------- TABLE_CATALOG def TABLE_SCHEMA mysql TABLE_NAME time_zone_transition TABLE_TYPE BASE TABLE ENGINE MyISAM VERSION 10 ROW_FORMAT Fixed TABLE_ROWS #TBLR# AVG_ROW_LENGTH #ARL# DATA_LENGTH #DL# MAX_DATA_LENGTH #MDL# INDEX_LENGTH #IL# DATA_FREE #DF# AUTO_INCREMENT NULL CREATE_TIME #CRT# UPDATE_TIME #UT# CHECK_TIME #CT# TABLE_COLLATION utf8_general_ci CHECKSUM NULL CREATE_OPTIONS #CO# TABLE_COMMENT #TC# user_comment Time zone transitions Separator ----------------------------------------------------- TABLE_CATALOG def TABLE_SCHEMA mysql TABLE_NAME time_zone_transition_type TABLE_TYPE BASE TABLE ENGINE MyISAM VERSION 10 ROW_FORMAT Fixed TABLE_ROWS #TBLR# AVG_ROW_LENGTH #ARL# DATA_LENGTH #DL# MAX_DATA_LENGTH #MDL# INDEX_LENGTH #IL# DATA_FREE #DF# AUTO_INCREMENT NULL CREATE_TIME #CRT# UPDATE_TIME #UT# CHECK_TIME #CT# TABLE_COLLATION utf8_general_ci CHECKSUM NULL CREATE_OPTIONS #CO# TABLE_COMMENT #TC# user_comment Time zone transition types Separator ----------------------------------------------------- TABLE_CATALOG def TABLE_SCHEMA mysql TABLE_NAME user TABLE_TYPE BASE TABLE ENGINE MyISAM VERSION 10 ROW_FORMAT Dynamic TABLE_ROWS #TBLR# AVG_ROW_LENGTH #ARL# DATA_LENGTH #DL# MAX_DATA_LENGTH #MDL# INDEX_LENGTH #IL# DATA_FREE #DF# AUTO_INCREMENT NULL CREATE_TIME #CRT# UPDATE_TIME #UT# CHECK_TIME #CT# TABLE_COLLATION utf8_bin CHECKSUM NULL CREATE_OPTIONS #CO# TABLE_COMMENT #TC# user_comment Users and global privileges Separator ----------------------------------------------------- DROP USER testuser1@localhost; CREATE USER testuser1@localhost; GRANT SELECT ON test1.* TO testuser1@localhost; # Establish connection testuser1 (user=testuser1) SELECT *, LEFT( table_comment, IF(INSTR(table_comment,'InnoDB free') = 0 AND INSTR(table_comment,'number_of_replicas') = 0, LENGTH(table_comment), INSTR(table_comment,'InnoDB free') + INSTR(table_comment,'number_of_replicas') - 1)) AS "user_comment", '-----------------------------------------------------' AS "Separator" FROM information_schema.tables WHERE table_schema = 'mysql' ORDER BY table_schema,table_name; # Switch to connection default and close connection testuser1 DROP USER testuser1@localhost; DROP DATABASE test1;