合 PG数据库对象类型和个数统计
👉 本文共约12个字，系统预计阅读时间或需1分钟。
目录
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 | lhrdb=# select lhrdb-# nsp.nspname as SchemaName lhrdb-# ,case cls.relkind lhrdb-# when 'r' then 'TABLE' lhrdb-# when 'm' then 'MATERIALIZED_VIEW' lhrdb-# when 'i' then 'INDEX' lhrdb-# when 'S' then 'SEQUENCE' lhrdb-# when 'v' then 'VIEW' lhrdb-# when 'c' then 'composite type' lhrdb-# when 't' then 'TOAST' lhrdb-# when 'f' then 'foreign table' lhrdb-# when 'p' then 'partitioned_table' lhrdb-# when 'I' then 'partitioned_index' lhrdb-# else cls.relkind::text lhrdb-# end as ObjectType, lhrdb-# COUNT(*) cnt lhrdb-# from pg_class cls lhrdb-# join pg_namespace nsp lhrdb-# on nsp.oid = cls.relnamespace lhrdb-# where nsp.nspname not in ('information_schema', 'pg_catalog') lhrdb-# and nsp.nspname not like 'pg_toast%' lhrdb-# GROUP BY nsp.nspname,cls.relkind lhrdb-# UNION all lhrdb-# SELECT n.nspname as "Schema", lhrdb-# CASE p.prokind lhrdb-# WHEN 'a' THEN 'agg' lhrdb-# WHEN 'w' THEN 'window' lhrdb-# WHEN 'p' THEN 'proc' lhrdb-# ELSE 'func' lhrdb-# END as "Type", lhrdb-# COUNT(*) cnt lhrdb-# FROM pg_catalog.pg_proc p lhrdb-# LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace lhrdb-# WHERE pg_catalog.pg_function_is_visible(p.oid) lhrdb-# AND n.nspname not in ('information_schema', 'pg_catalog') lhrdb-# GROUP BY n.nspname ,p.prokind; schemaname | objecttype | cnt ------------+-------------------+----- public | composite type | 1 public | VIEW | 1 public | INDEX | 1 public | partitioned_table | 1 public | TABLE | 9 public | SEQUENCE | 1 public | proc | 1 public | func | 3 (8 rows) |
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
