PostgreSQL all tables count Row

drop table zzz_table_schema_rows_n;

create table zzz_table_schema_rows_n as 
WITH tbl AS
  (SELECT table_schema,
          TABLE_NAME
   FROM information_schema.tables
   WHERE
    1=1
    AND TABLE_NAME not like 'pg_%'
    AND TABLE_TYPE='BASE TABLE'
    AND table_schema in ('uyumsoft'))
SELECT table_schema,
       TABLE_NAME,
       (xpath('/row/c/text()', query_to_xml(format('select count(*) as c from %I.%I', table_schema, TABLE_NAME), FALSE, TRUE, '')))[1]::text::int AS rows_n
FROM tbl
ORDER BY rows_n DESC;

lock yok

SELECT
    n.nspname  AS table_schema,
    c.relname  AS table_name,
    c.reltuples::bigint AS rows_n
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
  AND n.nspname = 'uyumsoft'
  AND c.relname NOT LIKE 'pg_%'
ORDER BY rows_n DESC;

`WITH tables AS (
    SELECT table_schema, table_name
    FROM information_schema.tables
    WHERE table_type = 'BASE TABLE'
      AND table_schema = 'uyumsoft'
      AND table_name NOT LIKE 'pg_%'
)
SELECT
    t.table_schema,
    t.table_name,
    (
        xpath(
            '/row/c/text()',
            query_to_xml(
                format(
                    'SELECT count(*) AS c FROM %I.%I',
                    t.table_schema,
                    t.table_name
                ),
                false, true, ''
            )
        )
    )[1]::text::bigint AS rows_n
FROM tables t
ORDER BY rows_n DESC;
`

select * from zzz_table_schema_rows_n

Comments

[Fatih Şahin]

stackoverflow.com/questions/259667...