查询数据库信息

查看所有数据库容量大小

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
where 1 = 1
    -- and table_schema IN ('jms')
group by table_schema
order by sum(data_length) desc, sum(index_length) desc;

查看所有数据库各表容量大小

select 
    table_schema as '数据库',
    table_name as '表名',
    table_rows as '记录数',
    truncate(data_length/1024/1024, 2) as '数据容量(MB)',
    truncate(index_length/1024/1024, 2) as '索引容量(MB)'
from information_schema.tables
where 1 = 1
    -- and table_schema IN ('jms')
order by data_length desc, index_length desc;

查看所有数据库字段的基本信息

SELECT
    -- T.TABLE_SCHEMA AS '库名',
    -- T.TABLE_NAME AS '表名',
    -- T.TABLE_COMMENT AS '表注释',
    (CASE WHEN C.ORDINAL_POSITION=1 THEN CONCAT(T.TABLE_SCHEMA ,'.', T.TABLE_NAME) ELSE '' END) AS '库名.表名',
    (CASE WHEN C.ORDINAL_POSITION=1 THEN T.TABLE_COMMENT ELSE '' END) 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 1 = 1
    -- AND T.TABLE_SCHEMA IN ('jms')
ORDER BY
    C.TABLE_NAME,
    C.ORDINAL_POSITION;