pgsql查询所有表以及行数

最新推荐文章于 2024-06-27 09:40:58 发布

小杨同学~

最新推荐文章于 2024-06-27 09:40:58 发布

阅读量3.6k

点赞数

分类专栏： Postgresql 文章标签：数据库 sql java

原文链接：https://www.modb.pro/db/13644

版权

Postgresql 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

pgsql查询所有表以及行数

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 -