show size of database tables

table_size_mysql.sql

-- https://dev.mysql.com/doc/refman/8.0/en/tables-table.html
SELECT
  table_name,
  table_rows AS num_rows,
  ROUND((data_length + index_length) / 1024 / 1024) AS size_mb
FROM
  information_schema.TABLES
WHERE
  TABLE_SCHEMA = 'your_schema_here'
ORDER BY
  size_mb
DESC;

table_size_redshift.sql

-- adapted from https://github.com/awslabs/amazon-redshift-utils/blob/master/src/AdminScripts/table_info.sql
SELECT TRIM(pgn.nspname) AS SCHEMA,
       TRIM(a.name) AS TABLE,
       CASE WHEN pgc.reldiststyle = 8 THEN a.rows_all_dist ELSE a.rows END AS num_rows,
       b.mbytes AS size_mb
FROM (SELECT
        db_id,
        id,
        name,
        SUM(ROWS) AS ROWS,
        MAX(ROWS) AS rows_all_dist
      FROM pg_catalog.stv_tbl_perm a
      GROUP BY db_id, id, name) AS a
  JOIN pg_class AS pgc ON pgc.oid = a.id
  JOIN pg_namespace AS pgn ON pgn.oid = pgc.relnamespace
  LEFT OUTER JOIN (SELECT tbl, COUNT(*) AS mbytes FROM stv_blocklist GROUP BY tbl) b ON a.id = b.tbl
WHERE mbytes IS NOT NULL
AND pgn.nspname = 'your_schema_here'
AND pgc.relowner > 1
ORDER BY size_mb DESC;