一键查询PostgreSQL模式中所有表行数和表大小

经常有人问如何一次性精确查询数据库中每个表的大小，这里写了个脚本方便大家使用。

1、如果使用的是图形化客户端，首先运行下面的语句，然后复制运行结果放新窗口中运行即可

SELECT CASE
           WHEN t.row_total = 1 THEN
            'select * from(' || REPLACE(sql_content, ' union all ', ') a order by 2 desc;')
           WHEN t.row_total = t.row_seq THEN
            REPLACE(sql_content, ' union all ', ') a order by 2 desc;')
           WHEN t.row_seq = 1 THEN
            'select * from(' || sql_content
           ELSE
            sql_content
       END sql_content
  FROM (SELECT COUNT(*) over() row_total,
               row_number() over() row_seq,
               'SELECT ''' || quote_ident(tablename) ||
               ''' 表名, count(*) 表行数,pg_size_pretty(pg_total_relation_size(''' ||
               quote_ident(tablename) || '''::regclass)) 表总大小 from ' || quote_ident(tablename) ||
               ' union all ' sql_content
          FROM pg_tables
         WHERE schemaname = 'public') t
 ORDER BY t.row_seq;

复制

2、如果使用的是命令行，则可以使用管道，一键查看

psql -qAtX -d test -U joan -c "SELECT CASE
           WHEN t.row_total = 1 THEN
            'select * from(' || REPLACE(sql_content, ' union all ', ') a order by 2 desc;')
           WHEN t.row_total = t.row_seq THEN
            REPLACE(sql_content, ' union all ', ') a order by 2 desc;')
           WHEN t.row_seq = 1 THEN
            'select * from(' || sql_content
           ELSE
            sql_content
       END sql_content
  FROM (SELECT COUNT(*) over() row_total,
               row_number() over() row_seq,
               'SELECT ''' || quote_ident(tablename) ||
               ''' 表名, count(*) 表行数,pg_size_pretty(pg_total_relation_size(''' ||
               quote_ident(tablename) || '''::regclass)) 表总大小 from ' || quote_ident(tablename) ||
               ' union all ' sql_content
          FROM pg_tables
         WHERE schemaname = 'public') t
 ORDER BY t.row_seq;" | psql -d test -U joan -f -

复制

说明：

1. 以上以public模式为例，烦请修改为你需要的模式；
2. 可以修改以上语句查询全库表大小；