SELECT tb.relname AS "name",
pg_size_pretty(pg_total_relation_size('"' || schemaname || '"."' || tb.relname || '"')) AS "reserved",
pg_size_pretty(pg_table_size('"' || schemaname || '"."' || tb.relname || '"')) AS "data",
pg_size_pretty(pg_indexes_size('"' || schemaname || '"."' || tb.relname || '"')) "index_size",
C.reltuples "sumrows",
'' "unused",
CAST(obj_description(relfilenode, 'pg_class') AS VARCHAR) "tdescription",
(SELECT A.attname AS COLUMN
FROM
pg_class C,
pg_attribute A
WHERE
C.relname = tb.relname
AND A.attnum > 0
AND A.attrelid = C.oid
AND(SELECT COUNT
(pg_constraint.* )
FROM
pg_constraint
INNER JOIN pg_class ON pg_constraint.conrelid = pg_class.oid
INNER JOIN pg_attribute ON pg_attribute.attrelid = pg_class.oid
AND pg_attribute.attnum = ANY(pg_constraint.conkey)
INNER JOIN pg_type ON pg_type.oid = pg_attribute.atttypid
WHERE
pg_class.relname = C.relname
AND pg_constraint.contype = 'p'
AND pg_attribute.attname = A.attname
) > 0
LIMIT 1
) AS "pk"
FROM
pg_stat_user_tables as tb
LEFT JOIN pg_class C ON C.relname = tb.relname
ORDER BY
tb.relname
pgsql(postgresql) 获取用户数据表,显示主键,说明,索引大小,表大小
最新推荐文章于 2024-07-23 13:38:48 发布