List all tables in your database - largest first

posted Apr 28, 2017, 10:20 AM by Sachchida Ojha
List all tables in your database - largest first - largest in terms of no or row or tuples

SELECT 
n.nspname AS schemaname, 
c.relname AS tablename,
c.relpages,
c. reltuples,  
pg_get_userbyid(c.relowner) AS tableowner, 
t.spcname AS tablespace, 
c.relhasindex AS hasindexes, 
c.relhasrules AS hasrules, 
c.reltriggers > 0 AS hastriggers
FROM pg_class c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace
WHERE c.relkind = 'r'::"char"
order by c.reltuples desc;

List all tables in your database - largest first - largest by no of relpages

SELECT 
n.nspname AS schemaname, 
c.relname AS tablename,
c.relpages,
c. reltuples,  
pg_get_userbyid(c.relowner) AS tableowner, 
t.spcname AS tablespace, 
c.relhasindex AS hasindexes, 
c.relhasrules AS hasrules, 
c.reltriggers > 0 AS hastriggers
FROM pg_class c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace
WHERE c.relkind = 'r'::"char"
order by c.relpages desc;
Comments