数据库表统计

Postgresql

SELECT * FROM pg_description;
SELECT * FROM pg_class
-- 1、查询某Schema下的每张表的记录数：
SELECT
    relname AS TABLE_NAME,
    CAST ( obj_description ( relfilenode, 'pg_class' ) AS VARCHAR ) AS COMMENT,
    ROUND( reltuples :: DECIMAL ) AS row_counter,
    relpages AS 磁盘大小,
    relkind AS 表类型 
FROM
    pg_class 
WHERE
    relkind = 'r' 
    AND relnamespace = ( SELECT oid FROM pg_namespace WHERE nspname = 'public' ) 
ORDER BY
    row_counter DESC;
-- r =普通表， i =索引，S =序列，v =视图，m =物化视图， c =复合类型，t = TOAST表，f =外部表
-- 2、查询表名和表注释
SELECT
    relname AS tabname,
    CAST ( obj_description ( relfilenode, 'pg_class' ) AS VARCHAR ) AS COMMENT 
FROM
    pg_class C 
WHERE
    relname = 'table_name';
-- 2、查询字段名、字段类型及字段长度和字段注释
SELECT A
    .attnum,
    A.attname,
    concat_ws ( '', T.typname, SUBSTRING ( format_type ( A.atttypid, A.atttypmod ) FROM '\(.*\)' ) ) AS TYPE,
    d.description 
FROM
    pg_class C,
    pg_attribute A,
    pg_type T,
    pg_description d 
WHERE
    C.relname = 'scjdgl_ztjbxx' 
    AND A.attnum > 0 
    AND A.attrelid = C.oid 
    AND A.atttypid = T.oid 
    AND d.objoid = A.attrelid 
    AND d.objsubid = A.attnum
-- 3、查询表的大小
select pg_size_pretty(pg_relation_size('table_name'));
-- 5、查出所有表按大小排序并分离data与index
SELECT
    table_name,
    pg_size_pretty(table_size) AS table_size,
    pg_size_pretty(indexes_size) AS indexes_size,
    pg_size_pretty(total_size) AS total_size
FROM (
    SELECT
        table_name,
        pg_table_size(table_name) AS table_size,
        pg_indexes_size(table_name) AS indexes_size,
        pg_total_relation_size(table_name) AS total_size
    FROM (
        SELECT ('"' || table_schema || '"."' || table_name || '"') AS table_name
        FROM information_schema.tables
    ) AS all_tables
    ORDER BY total_size DESC
) AS pretty_sizes
-- 6、统计各数据库占用的磁盘大小
SELECT d.datname AS Name,  pg_catalog.pg_get_userbyid(d.datdba) AS Owner,
    CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
        THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))
        ELSE 'No Access'
    END AS SIZE
FROM pg_catalog.pg_database d
    ORDER BY
    CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
        THEN pg_catalog.pg_database_size(d.datname)
        ELSE NULL
    END DESC -- nulls first
    LIMIT 20
/* 表名 字段 注释 类型*/
SELECT C
    .relname 表名,
    CAST ( obj_description ( relfilenode, 'pg_class' ) AS VARCHAR ) 名称,
    A.attname 字段,
    d.description 字段备注,
    concat_ws ( '', T.typname, SUBSTRING ( format_type ( A.atttypid, A.atttypmod ) FROM '\(.*\)' ) ) AS 列类型 
FROM
    pg_class C,
    pg_attribute A,
    pg_type T,
    pg_description d 
WHERE
    A.attnum > 0 
    AND A.attrelid = C.oid 
    AND A.atttypid = T.oid 
    AND d.objoid = A.attrelid 
    AND d.objsubid = A.attnum 
    AND C.relname IN ( SELECT tablename FROM pg_tables WHERE schemaname = 'public' AND POSITION ( '_2' IN tablename ) = 0 ) 
ORDER BY
    C.relname,
    A.attnum
/* 查询表名和表注释 */
SELECT relname,obj_description(oid)
FROM pg_class
WHERE relkind = 'r'     AND relname NOT LIKE'pg_%' 
    AND relname NOT LIKE'sql_%'

MySql

/* 根据库名获取所有表的信息 */
SELECT
    *
FROM
    information_schema.`TABLES`
WHERE
    TABLE_SCHEMA = 'pdp_in';

/* 根据库名获取所有表名称和表说明 */
SELECT
    TABLE_NAME,
    TABLE_COMMENT
FROM
    information_schema.`TABLES`
WHERE
    TABLE_SCHEMA = 'pdp_in';

/* 根据库名获取所有的字段信息 */
SELECT
    TABLE_SCHEMA AS '库名',
    TABLE_NAME AS '表名',
    COLUMN_NAME AS '列名',
    ORDINAL_POSITION AS '列的排列顺序',
    COLUMN_DEFAULT AS '默认值',
    IS_NULLABLE AS '是否为空',
    DATA_TYPE AS '数据类型',
    CHARACTER_MAXIMUM_LENGTH AS '字符最大长度',
    NUMERIC_PRECISION AS '数值精度(最大位数)',
    NUMERIC_SCALE AS '小数精度',
    COLUMN_TYPE AS 列类型,
    COLUMN_KEY 'KEY',
    EXTRA AS '额外说明',
    COLUMN_COMMENT AS '注释'
FROM
    information_schema.`COLUMNS`
WHERE
    TABLE_SCHEMA = 'pdp_in'
ORDER BY
    TABLE_NAME,
    ORDINAL_POSITION;

/* 根据库名获取所有的库和表字段的基本信息 */
SELECT
    C.TABLE_SCHEMA AS '库名',
    T.TABLE_NAME AS '表名',
    T.TABLE_COMMENT AS '表注释',
    C.COLUMN_NAME AS '列名',
    C.COLUMN_COMMENT AS '列注释',
    C.ORDINAL_POSITION AS '列的排列顺序',
    C.COLUMN_DEFAULT AS '默认值',
    C.IS_NULLABLE AS '是否为空',
    C.DATA_TYPE AS '数据类型',
    C.CHARACTER_MAXIMUM_LENGTH AS '字符最大长度',
    C.NUMERIC_PRECISION AS '数值精度(最大位数)',
    C.NUMERIC_SCALE AS '小数精度',
    C.COLUMN_TYPE AS 列类型,
    C.COLUMN_KEY 'KEY',
    C.EXTRA AS '额外说明'
FROM
    information_schema.`TABLES` T
LEFT JOIN information_schema.`COLUMNS` C ON T.TABLE_NAME = C.TABLE_NAME
AND T.TABLE_SCHEMA = C.TABLE_SCHEMA
WHERE
    T.TABLE_SCHEMA = 'pdp_in' 
ORDER BY
    C.TABLE_NAME,
    C.ORDINAL_POSITION;


-- 1. 查看所有数据库容量大小
SELECT
    table_schema AS '数据库',
    sum( table_rows ) AS '记录数',
    sum(
    TRUNCATE ( data_length / 1024 / 1024, 2 )) AS '数据容量(MB)',
    sum(
    TRUNCATE ( index_length / 1024 / 1024, 2 )) AS '索引容量(MB)' 
FROM
    information_schema.TABLES 
GROUP BY
    table_schema 
ORDER BY
    sum( data_length ) DESC,
    sum( index_length ) DESC;

--  2. 查看所有数据库各表容量大小
SELECT
    table_schema AS '数据库',
    table_name AS '表名',
    table_rows AS '记录数',
    TABLE_COMMENT,
    TRUNCATE ( data_length / 1024 / 1024, 2 ) AS '数据容量(MB)',
    TRUNCATE ( index_length / 1024 / 1024, 2 ) AS '索引容量(MB)',
--     DATA_LENGTH,
--     AVG_ROW_LENGTH,
    UPDATE_TIME
FROM
    information_schema.TABLES
WHERE table_schema = 'pdp_in'
ORDER BY
    data_length DESC,
    index_length DESC;

-- 一共有多少个字段
select count(*) from information_schema.COLUMNS where TABLE_SCHEMA='pdp_in'