人大金仓数据库常用sql

--查询模式表名及表数据行数

analyze;

SELECT n.nspname as "Schema",

       c.relname as "Name",

       sys_get_userbyid(c.relowner) as "Owner",

       obj_description(c.oid, 'SYS_CLASS') as "Description",

    c.reltuples as "tablesnums"

  FROM sys_class c

  LEFT JOIN sys_namespace n

    ON n.oid = c.relnamespace

WHERE c.relkind = 'r'

   AND n.nspname ~* 'PUBLIC'

ORDER BY 5 desc,1,2;

--当前库的PUBLIC模式数据表占用空间

SELECT

    n.nspname || '.' || c.relname AS table_name,

    sys_size_pretty(sys_total_relation_size('"' || n.nspname || '"."' || c.relname || '"')) AS table_size

FROM sys_class c,

     sys_namespace n

where n.oid = c.relnamespace

  and c.relkind='r'

  and n.nspname~*'PUBLIC'

ORDER BY 2 DESC

;