How to count all rows and column of the tables in the database?

Question

I want to show all columns of the table and count the rows of all table in database.

I am using this function which give me the name of the table and row count of the table

CREATE OR REPLACE FUNCTION count_em_all () RETURNS SETOF table_count  AS '
DECLARE 
    the_count RECORD; 
    t_name RECORD; 
    t_column RECORD;
    r table_count%ROWTYPE; 

BEGIN
    FOR t_name IN 
        SELECT *
        FROM information_schema.tables
        where table_schema !=''pg_catalog''
          and table_schema !=''information_schema''
        ORDER BY 1,2
        LOOP
            FOR the_count IN EXECUTE ''SELECT COUNT(*) AS "count" FROM '' || t_name.table_schema||''.''||t_name.table_name
            LOOP 
            END LOOP; 

            r.table_schema := t_name.table_schema;
            r.table_name := t_name.table_name; 
            r.num_rows := the_count.count; 
            RETURN NEXT r; 
        END LOOP; 
        RETURN; 
END;
' LANGUAGE plpgsql;

But I need table column name also

select table_schema, 
       table_name, column_name
       (xpath('/row/cnt/text()', xml_count))[1]::text::int as row_count
from (
  select table_name, table_schema, 
         query_to_xml(format('select count(*) as cnt from %I.%I', table_schema, table_name), false, true, '') as xml_count
  from information_schema.tables
  where table_schema = 'public' --<< change here for the schema you want
) t

Error:

 syntax error at or near "["
LINE 3:        (xpath('/row/cnt/text()', xml_count))[1]::text::int a...

Do you mean you want another column in the output that contains a comma separated list of the column names for each table? You could extend [this answer](https://stackoverflow.com/a/38684225) to include that — , Sep 06 '19 at 11:55
What does "Table Column Name" mean. Please provide sample input and sample output so we can help better. Otherwise we are left to guess what you mean. — JNevill, Sep 06 '19 at 13:06
@a_horse_with_no_name: I try to extend but couldn't get any column name — sumit kundan, Sep 07 '19 at 05:19
@a_horse_with_no_name: I edit my question please what i try its giving me an error — sumit kundan, Sep 07 '19 at 05:21

score 0 · Accepted Answer · answered Sep 07 '19 at 05:59

You need to join the result of the query to information_schema.columns and aggregate the columns into a string. It's easier to do the aggregation in a derived table (aka "sub-query"):

select t.table_schema, 
       t.table_name, 
       (xpath('/row/cnt/text()', xml_count))[1]::text::int as row_count,
       c.columns
from (
  select table_name, table_schema, 
         query_to_xml(format('select count(*) as cnt from %I.%I', table_schema, table_name), false, true, '') as xml_count
  from information_schema.tables
  where table_schema = 'public' --<< change here for the schema you want
) t
  join (
    select col.table_schema, col.table_name, 
           string_agg(col.column_name::text, ',' order by col.ordinal_position) as columns
    from information_schema.columns col
    group by col.table_schema, col.table_name
  ) c on t.table_schema = c.table_schema
     and t.table_name = c.table_name;

How to count all rows and column of the tables in the database?

1 Answers1