User:Breinbaas/scratch/dn.sh

#!/bin/sh

echo

echo "

select 
       catalog_name 
    , 'catalog_name, current_catalog, current_database()' as \"a.k.a.\"

from
       information_schema.information_schema_catalog_name
;

select
      schema
    , (select pg_catalog.pg_get_userbyid(n.nspowner) from pg_catalog.pg_namespace n where n.nspname = schema)        as owner 
    , to_char(      size, '99' || repeat('G999',3)) || '' || lpad(pg_size_pretty(       size::bigint ), 10) ||' '    as size
    , to_char(total_size, '99' || repeat('G999',3)) || '' || lpad(pg_size_pretty( total_size::bigint ), 10) ||'  '   as total_size
    , substring(current_setting('data_directory') from E'(pgsql\..*?)/') as instance
    , current_setting('port')                   as port
    , table_number                              as \"#\"
    , substring(tables::text, 1, 76)            as tables

from (
      select 
            table_schema	                                                                              as schema
          , sum( pg_relation_size      (table_catalog || '.' || table_schema || '.' || table_name) )    as size
          , sum( pg_total_relation_size(table_catalog || '.' || table_schema || '.' || table_name) )    as total_size
          , count( table_catalog || '.' || table_schema || '.' || table_name)                           as table_number
          , array_agg(table_name::text order by table_name::text)                                       as tables
      from information_schema.tables
      where table_type = 'BASE TABLE'
        and table_schema !~ '^pg_'
        and table_schema <> 'information_schema'
      group by 1
      order by 1
)
    as f
;

" | psql -X | less -SF