greenplum列出所有表并显示表的相关信息

最新推荐文章于 2024-03-04 18:44:07 发布

wapecheng

最新推荐文章于 2024-03-04 18:44:07 发布

阅读量1.7k

点赞数

CC 4.0 BY-SA版权

分类专栏：学习笔记文章标签：数据库 greenplum postgresql

本文链接：https://blog.youkuaiyun.com/wapecheng/article/details/126285100

学习笔记专栏收录该内容

57 篇文章

订阅专栏

本文介绍了一种查询Greenplum数据库中public模式下所有表的方法，包括表的DDL与DML最近操作记录、表占用的空间大小及行数等信息。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

列出greenplum中public下所有表，并显示最近的DDL与DML操作，以及显示表所占用空间大小以及表行数

select 
tables.*,
po.last_staactionname,
po.last_statime,
po1.first_staactionname,
po1.first_statime,
reltuples
from 
(select  
schemaname,SPLIT_PART(tablename, '_1', 1) as tablename,tableowner,tablespace,hasindexes,hasrules,hastriggers,
round(sum(pg_total_relation_size(schemaname||'.'||SPLIT_PART(tablename, '_1', 1)))/1024/1024,2) as  tablesize_KB
from pg_tables where schemaname in ('public')
 group by 1,2 ,3,4,5,6,7 order by tablesize_KB desc
)tables
left join 
(select objid,last_staactionname ,last_statime from (select objid,staactionname as last_staactionname,statime  as last_statime,
ROW_NUMBER()OVER(PARTITION by objid order by statime desc) as ind from pg_stat_last_operation where staactionname not in ('ANALYZE' ,'VACUUM','PRIVILEGE'))a where ind = 1) po
on tables.tablename::regclass = po.objid
left join
(select objid,first_staactionname ,first_statime  from (select objid,staactionname as first_staactionname,statime as first_statime,
ROW_NUMBER()OVER(PARTITION by objid order by statime) as ind from pg_stat_last_operation where staactionname not in ('ANALYZE' ,'VACUUM','PRIVILEGE'))a where ind = 1) po1
on tables.tablename::regclass = po1.objid
left join 
(SELECT SPLIT_PART(relname, '_1', 1) as relname, sum(reltuples) as reltuples
FROM pg_class r 
JOIN pg_namespace n
ON (relnamespace = n.oid)
WHERE relkind = 'r' AND n.nspname = 'public'
GROUP BY 1
)nsp 
on  tables.tablename = nsp.relname
;