【openGauss】运维常用的SQL

一、查模式

SELECT pn.oid AS schema_oid, iss.catalog_name, iss.schema_owner, iss.schema_name
FROM information_schema.schemata iss
INNER JOIN pg_namespace pn ON pn.nspname = iss.schema_name;

二、查对象

1. 查看某模式下的表名
   select tablename from pg_tables where schemaname = 'hsjc_bi';

select schemaname,tablename
from pg_tables
where schemaname = 'xxx' and tablename like 'fact_%';

select 'truncate table ' || schemaname || '.' || tablename || ';'
from pg_tables
where schemaname = 'xxx' and tablename like 'fact_%';

2. 查看某表的字段

SELECT
A.attname AS NAME,
format_type(A.atttypid, A.atttypmod) AS TYPE,
A.attnotnull AS NOTNULL,
col_description(A.attrelid, A.attnum) AS COMMENT
FROM
pg_class AS C,
pg_attribute AS A
WHERE
C.relname = 'tableName'
AND A.attnum > 0
AND A.attrelid = C.oid

3. 查询数据表名称及中文备注、每个表的记录数

SELECT a.relname AS name,
b.description AS comment,
a.reltuples
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') AND a.relkind='r'
ORDER BY a.relname;

3. 查某表的索引

二、查大小

1. 查看所有表的表大小

select table_schema,
TABLE_NAME,
reltuples,
pg_size_pretty(pg_total_relation_size('"'||table_schema||'"."'||table_name||'"'))
from pg_class, information_schema.tables
where relname = TABLE_NAME
ORDER BY reltuples desc
limit 20;

查看某表的索引