MYSQL数据字典

1 查询表和列

 1 --最齐全的信息查询
 2 SELECT a.TABLE_NAME "表",
 3 a.COLUMN_NAME "列",
 4 a.COLUMN_TYPE "类型",
 5 a.COLUMN_DEFAULT "默认值",
 6 a.IS_NULLABLE "是否为空",
 7 a.CHARACTER_SET_NAME "表字符集",
 8 a.COLLATION_NAME "校验字符集",
 9 CONCAT_WS(',',a.COLUMN_COMMENT,a.COLUMN_KEY, a.EXTRA) "列备注",
10 b.TABLE_COMMENT "表备注" ,
11 b.ENGINE "引擎"
12 FROM information_schema.COLUMNS a,information_schema.TABLES b
13 WHERE a.TABLE_SCHEMA=b.TABLE_SCHEMA
14 AND a.TABLE_SCHEMA='db1'
15 AND a.TABLE_NAME=b.TABLE_NAME
16 order by a.ORDINAL_POSITION;

2 查询索引信息

 1 SELECT
 2 TABLE_SCHEMA,
 3 TABLE_NAME,
 4 NON_UNIQUE,
 5 INDEX_NAME,
 6 SEQ_IN_INDEX,
 7 COLUMN_NAME,
 8 INDEX_TYPE,
 9 CONCAT(COMMENT,INDEX_COMMENT) INDEX_COMMENT
10 FROM INFORMATION_SCHEMA.STATISTICS
11 WHERE TABLE_SCHEMA = 'db1'
12 ORDER BY TABLE_SCHEMA,TABLE_NAME,INDEX_NAME,SEQ_IN_INDEX;