查看常用数据库库表数据量

MySQL

        mysql -h localhost -P 3306 -u root -p 123456

        show databases;

-- 查看指定库表名、数据量

            SELECT TABLE_NAME, TABLE_ROWS

            FROM INFORMATION_SCHEMA.TABLES

            WHERE TABLE_SCHEMA = 'test';   -- 库名

-- pg数据库

        psql -d 192.168.1.9 -p 5432 -U szjt_test

 

        select table_name,table_catalog,table_type from information_schema.tables where table_schema = 'public';

        SELECT table_name FROM information_schema.tables where table_name like 'test_user%' and table_schema = 'public';

        select count(*) from  test;

 

-- 查询所有库表名、数据量大小

            SELECT a.relname AS name,

                b.description AS comment,

                a.oid

            FROM pg_class a

                LEFT OUTER JOIN pg_description b ON b.objsubid=0 AND a.oid = b.objoid

            WHERE a.relnamespace = (SELECT oid FROM pg_namespace WHERE nspname='public') --用户表一般存储在public模式下

            AND a.relkind='r'

            ORDER BY a.relname;

-- 查询所有库表名、数据量大小

            select

                t.schemaname as 模式名,

                t.relname as 表名,

                b.description AS comment,

                t.n_live_tup as 行数

            from

                pg_stat_user_tables t

            where

                t.schemaname = 'public'  --模式名

            order by

                t.relname

-- 查询指定模式下的表名、注释、行数

            with m as

            (SELECT a.relname AS name,

                b.description AS comment,

                a.oid

            FROM pg_class a

                LEFT OUTER JOIN pg_description b ON b.objsubid=0 AND a.oid = b.objoid

            WHERE a.relnamespace = (SELECT oid FROM pg_namespace WHERE nspname='public') --用户表一般存储在public模式下

            AND a.relkind='r'

            ORDER BY a.relname

            )

 

            select

                t.schemaname as 模式名,

                t.relname as 表名,

                m.comment as 注释,

                t.n_live_tup as 行数

            from

                pg_stat_user_tables t

                left join m on m.name=t.relname

            where

                t.schemaname = 'public'  --模式名

            order by

                t.relname

-- kingbase