MySQL 查询数据所有表和表结构信息

SELECT

t.table_name as '表',

t.table_comment as '表名',

c.ORDINAL_POSITION AS '序号',

c.COLUMN_NAME AS '字段名',

c.COLUMN_TYPE AS '类型(长度)',

c.NUMERIC_PRECISION AS '精度',

c.NUMERIC_SCALE AS '小数位数',

c.COLUMN_DEFAULT AS '默认值',

IF( c.IS_NULLABLE = 'YES', '√', '' ) AS '允许空',

IF( c.COLUMN_KEY = 'PRI', '√', '' ) AS '主键',

c.COLUMN_COMMENT AS '说明'

FROM

INFORMATION_SCHEMA.COLUMNS c

LEFT JOIN INFORMATION_SCHEMA.TABLES t

on c.table_name=t.table_name and t.table_schema=c.table_schema

WHERE

c.table_schema = '数据库名称' -- 数据库名称

ORDER BY t.table_name, c.ORDINAL_POSITION ASC

-- AND table_name = '表名'

-- 查询数据库库所有表

SELECT t.table_name as '表',

t.table_comment as '表名' FROM INFORMATION_SCHEMA.TABLES t

WHERE table_schema = '数据库名称'