数据库信息统计

人大金仓、达梦和mysql查询数据库下的库名，表名，表的行数和表大小

PG/人大金仓在对应的库下面去查询

SELECT
    nspname AS schema_name,
    c.relname AS table_name,
    ROUND(pg_relation_size(c.oid) / 1024.0 / 1024.0, 2) AS size,
    pg_stat_user_tables.n_live_tup AS row_count,
    COUNT(a.attnum) AS column_count
FROM
    pg_class c
JOIN
    pg_namespace n ON n.oid = c.relnamespace
JOIN
    pg_stat_user_tables ON pg_stat_user_tables.relid = c.oid
LEFT JOIN
    pg_attribute a ON a.attrelid = c.oid AND a.attnum > 0 AND NOT a.attisdropped
WHERE
    c.relkind = 'r' -- 只选择普通表
GROUP BY
    nspname, c.relname, c.oid, pg_stat_user_tables.n_live_tup
ORDER BY
    nspname, c.relname;

达梦用SYSDBA用户查询

SELECT
    t.owner TABLE_SCHEMA,
    t.TABLE_NAME,
    t.num_rows TABLE_ROWS,
    s.BYTES / 1024 / 1024 TABLE_VOLUME,  -- 以M为单位
    COUNT(c.column_name) COLUMN_COUNT    -- 新增字段数量列
FROM
    dba_tables t
LEFT JOIN
    dba_segments s ON t.table_name = s.segment_name AND t.owner = s.owner
LEFT JOIN
    dba_tab_columns c ON t.table_name = c.table_name AND t.owner = c.owner
WHERE
    t.owner = 'SAAS_APLIM'  --需要改库名
GROUP BY
    t.owner, t.TABLE_NAME, t.num_rows, s.BYTES  -- 按主表信息分组
ORDER BY
    TABLE_VOLUME DESC;

MYSQL

SELECT 
  t.TABLE_NAME AS 表名, 
  COUNT(c.COLUMN_NAME) AS 字段数, 
  t.TABLE_ROWS AS 数据量（估算）, 
  CONCAT(ROUND(t.DATA_LENGTH / 1024 / 1024, 2), ' MB') AS 表大小（数据）, 
  CONCAT(ROUND(t.INDEX_LENGTH / 1024 / 1024, 2), ' MB') AS 索引大小 
FROM 
  information_schema.TABLES t 
JOIN 
  information_schema.COLUMNS c ON t.TABLE_SCHEMA = c.TABLE_SCHEMA AND t.TABLE_NAME = c.TABLE_NAME 
WHERE 
  t.TABLE_SCHEMA = 'ag_bigdata_ods' 
GROUP BY 
  t.TABLE_NAME, t.TABLE_ROWS, t.DATA_LENGTH, t.INDEX_LENGTH 
ORDER BY 
  t.DATA_LENGTH DESC;