[MySQL]: SQL script to list MyISAM & innodb_file_per_tables

-- It needs to be run from an account that has read permission for the INFORMATION_SCHEMA database.

select msg as `==> Checking current version of MySQL.`
from
  (
  select
    'This script should be run on MySQL version 5.6. ' +
    'Earlier versions are not supported.' as msg,
    cast(substring_index(version(), '.', 1) as unsigned) * 100 + 
      cast(substring_index(substring_index(version(), '.', 2), '.', -1) 
      as unsigned) 
    as major_minor
  ) as T
where major_minor <> 506;


-- List MyISAM tables and compressed tables. Include the table size.

select concat(TABLE_SCHEMA, '.', TABLE_NAME) as `==> MyISAM Tables.`,
round(((data_length + index_length) / 1024 / 1024), 2) "Approx size (MB)"
from INFORMATION_SCHEMA.TABLES
where
  ENGINE <> 'InnoDB'
  and
  (
    -- User tables
    TABLE_SCHEMA not in ('mysql', 'performance_schema', 
                         'information_schema')
    or
    -- Non-standard system tables
    (
      TABLE_SCHEMA = 'mysql' and TABLE_NAME not in
        (
          'columns_priv', 'db', 'event', 'func', 'general_log',
          'help_category', 'help_keyword', 'help_relation',
          'help_topic', 'host', 'ndb_binlog_index', 'plugin',
          'proc', 'procs_priv', 'proxies_priv', 'servers', 'slow_log',
          'tables_priv', 'time_zone', 'time_zone_leap_second',
          'time_zone_name', 'time_zone_transition', 
          'time_zone_transition_type', 'user'
        )
    )
  );


-- List tables created with innodb_file_per_table enabled. Include table size.

select NAME as `==> Tables not in a single tablespace.`,
round(((data_length + index_length) / 1024 / 1024), 2) "Approx size (MB)"
from INFORMATION_SCHEMA.INNODB_SYS_TABLES
join INFORMATION_SCHEMA.TABLES on 
     INNODB_SYS_TABLES.name = concat(TABLES.table_schema,'/',TABLES.table_name)
where SPACE <> 0;

The script produces output as shown in the following example. The output also includes the approximate size of each table in MB.

+——————–+——————+

| ==> MyISAM Tables. | Approx size (MB) |
+--------------------+------------------+
| test.my_table      |            65.25 |
+--------------------+------------------+
1 row in set (0.01 sec)

+----------------------------------------+------------------+
| ==> Tables not in a single tablespace. | Approx size (MB) |
+----------------------------------------+------------------+
| test.my_other_table                    |           105.10 |
| mysql.innodb_index_stats               |             0.02 |
| mysql.innodb_table_stats               |             0.02 |
+----------------------------------------+------------------+