Skip to content

Instantly share code, notes, and snippets.

@habedi

habedi/get_tables_info.sql

Last active December 12, 2024 10:23
Show Gist options
  • Save habedi/f93ee3de6622acc366fc0ff06b61b279 to your computer and use it in GitHub Desktop.
Save habedi/f93ee3de6622acc366fc0ff06b61b279 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. habedi revised this gist Dec 12, 2024. 1 changed file with 2 additions and 0 deletions.
    2 changes: 2 additions & 0 deletions get_tables_info.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -1,3 +1,5 @@
    -- Note that numbers are estimates, so the exact number of rows or index size might slightly differ from the number shown

    -- For MySQL or MariaDB
    select
    table_name as `table name`,
  2. habedi revised this gist Dec 12, 2024. 1 changed file with 3 additions and 2 deletions.
    5 changes: 3 additions & 2 deletions get_tables_info.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -8,7 +8,8 @@ select
    from
    information_schema.tables
    where
    table_schema = coalesce(:schema_name, 'services')
    -- table_schema = coalesce(:schema_name, 'services') -- Doesn't work in `mysql`
    table_schema = 'services'
    order by
    `total size (gb)` desc;

    @@ -26,7 +27,7 @@ from
    join
    pg_namespace n on n.oid = c.relnamespace
    where
    -- n.nspname = coalesce(:schema_name, 'services') -- Doesn't work in psql
    -- n.nspname = coalesce(:schema_name, 'services') -- Doesn't work in `psql`
    n.nspname = 'services' -- replace with your schema name
    and c.relkind = 'r' -- only regular tables
    order by
  3. habedi revised this gist Dec 12, 2024. 1 changed file with 2 additions and 0 deletions.
    2 changes: 2 additions & 0 deletions get_tables_info.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -12,6 +12,8 @@ where
    order by
    `total size (gb)` desc;

    --------------------------------------------------------------------------------------------------------------------

    -- For PostgreSQL
    select
    c.relname as "table name",
  4. habedi renamed this gist Dec 12, 2024. 1 changed file with 0 additions and 0 deletions.
    0 get_table_info.sql → get_tables_info.sql
    View file
    File renamed without changes.
  5. habedi revised this gist Dec 12, 2024. 1 changed file with 19 additions and 0 deletions.
    19 changes: 19 additions & 0 deletions get_table_info.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -1,3 +1,4 @@
    -- For MySQL or MariaDB
    select
    table_name as `table name`,
    table_rows as `row count`,
    @@ -10,3 +11,21 @@ where
    table_schema = coalesce(:schema_name, 'services')
    order by
    `total size (gb)` desc;

    -- For PostgreSQL
    select
    c.relname as "table name",
    coalesce(nullif(c.reltuples, -1), 0)::bigint as "row count",
    round(pg_table_size(c.oid) / (1024.0 * 1024 * 1024), 2) as "data size (gb)",
    round(pg_indexes_size(c.oid) / (1024.0 * 1024 * 1024), 2) as "index size (gb)",
    round(pg_total_relation_size(c.oid) / (1024.0 * 1024 * 1024), 2) as "total size (gb)"
    from
    pg_class c
    join
    pg_namespace n on n.oid = c.relnamespace
    where
    -- n.nspname = coalesce(:schema_name, 'services') -- Doesn't work in psql
    n.nspname = 'services' -- replace with your schema name
    and c.relkind = 'r' -- only regular tables
    order by
    "total size (gb)" desc;
  6. habedi revised this gist Dec 10, 2024. No changes.
  7. habedi created this gist Dec 9, 2024.
    12 changes: 12 additions & 0 deletions get_table_info.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,12 @@
    select
    table_name as `table name`,
    table_rows as `row count`,
    round(data_length / (1024 * 1024 * 1024), 2) as `data size (gb)`,
    round(index_length / (1024 * 1024 * 1024), 2) as `index size (gb)`,
    round((data_length + index_length) / (1024 * 1024 * 1024), 2) as `total size (gb)`
    from
    information_schema.tables
    where
    table_schema = coalesce(:schema_name, 'services')
    order by
    `total size (gb)` desc;