Show all tables and their corresponding innodb_table_per_file files (if present)

file_per_table.sql

-- Show all tables and their corresponding innodb_table_per_file files (if present)
SELECT *
FROM (
  SELECT 
    TABLE_SCHEMA as 'Schema',
    TABLE_NAME as 'Table',
    ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024) as 'Info MB',
    ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 6) as 'Info GB',
    (DATA_LENGTH + INDEX_LENGTH) / 1099511627776 as 'Info TiB'
  FROM INFORMATION_SCHEMA.TABLES
  WHERE DATA_LENGTH + INDEX_LENGTH > 1
) ist
LEFT JOIN (
  SELECT
    ROUND(FILE_SIZE / 1024) as 'File MB',
    ROUND(FILE_SIZE / 1024 / 1024) as 'File GB',
    FILE_SIZE / 1099511627776 as 'File TiB',
    NAME as 'File'
  FROM INFORMATION_SCHEMA.INNODB_TABLESPACES -- For MySQL 8+
) isit
ON isit.File = CONCAT(ist.Schema, '/', ist.Table)
ORDER BY 3 DESC;