2

I can get the number of columns in all the tables in a postgresql database by

SELECT TABLE_SCHEMA, TABLE_NAME, COUNT(*) 
FROM INFORMATION_SCHEMA.COLUMNS 
GROUP BY TABLE_SCHEMA, TABLE_NAME;

I can get the number of rows in a specific table in a database by

SELECT  COUNT(*) FROM mytable

How to obtain column and row counts for all table in a database in one query?

00__00__00
  • 4,834
  • 9
  • 41
  • 89
  • 2
    Given that Oracle doesn't support `INFORMATION_SCHEMA.COLUMNS`, your question is suspicious. Please tag with the database youa re really using. – Gordon Linoff Sep 22 '20 at 11:49
  • https://stackoverflow.com/questions/2596670/how-do-you-find-the-row-count-for-all-your-tables-in-postgres –  Sep 22 '20 at 12:05
  • @a_horse_with_no_name I can not find an answer at that page i.e. how to get row and column counts at once – 00__00__00 Sep 22 '20 at 12:08
  • 1
    All of those answer will give you the row count for each table. They can easily be extended to include the column count (especially [my answer](https://stackoverflow.com/a/38684225)) –  Sep 22 '20 at 12:09
  • the "easy extension" is in fact my original question thanks! – 00__00__00 Sep 22 '20 at 12:10

2 Answers2

1

Combine your query using a CTE and join it with the one posted on this answer, as @a_horse_with_no_name suggested, e.g.

WITH j AS (
  SELECT table_schema, table_name, count(*) AS count_columns
  FROM information_schema.columns
  GROUP BY table_schema, table_name
) 
SELECT 
  nspname AS schemaname,relname,reltuples,count_columns
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
JOIN j ON j.table_name = relname AND j.table_schema = nspname 
WHERE 
  nspname NOT IN ('pg_catalog', 'information_schema') AND
  relkind='r' 
ORDER BY reltuples DESC;
Jim Jones
  • 18,404
  • 3
  • 35
  • 44
0

You could either enter \d+ in the selected database using psql from the command line or use the SQL statements from here:

https://wiki.postgresql.org/wiki/Disk_Usage

Simon Schiff
  • 711
  • 6
  • 13