mysql 数据库表信息查询语句

做项目的遇到客户要统计 mysql 数据表的信息的情况，但是数据库中的表数量太多，所以找了一种便捷的方式快速查询出这些信息，下面是 SQL

-- 查询表信息
SELECT
    TABLE_NAME AS 表名,
    TABLE_COMMENT AS 表注释
FROM
    INFORMATION_SCHEMA.TABLES
WHERE
    TABLE_SCHEMA = '数据库名'
ORDER BY TABLE_NAME;

-- 查询表索引
SELECT
    TABLE_NAME,
    COLUMN_NAME,
	INDEX_NAME,
    INDEX_TYPE,
	IF(NON_UNIQUE, '否', '是')
FROM
    INFORMATION_SCHEMA.STATISTICS
WHERE
    TABLE_SCHEMA = '数据库名';

-- 数据库所有表字段信息查询
SELECT DISTINCT
	a.table_name 表名,
	a.table_comment 表说明,
	b.COLUMN_NAME 字段名,
	b.column_comment 字段说明,
	SUBSTRING_INDEX( SUBSTRING_INDEX( b.column_type, ' ', - 1 ), '(', 1 ) AS 字段类型,
	CASE 
	WHEN b.column_type REGEXP '[0-9]' 
	THEN SUBSTRING_INDEX( SUBSTRING_INDEX( b.column_type, '(', - 1 ), ')', 1 ) 
	ELSE '' 
	END AS 字段长度,
	b.numeric_scale AS 小数位数,
	CASE 
	WHEN b.column_key = 'PRI' 
	THEN '是' ELSE '否' 
	END AS 是否主键 
FROM
	information_schema.TABLES a
	LEFT JOIN information_schema.COLUMNS b 
	ON a.table_name = b.TABLE_NAME 
WHERE
	a.table_schema = '数据库名' 
	and b.table_schema = a.table_schema
ORDER BY
	a.table_name;