查看mysql的表结构

查看mysql的所有表结构，（一般用来生成数据字典），假设TABLE_SCHEMA也就是数据库名称为asset，则SQL如下

SELECT
	a.TABLE_SCHEMA AS '数据库',
	'资产库' AS '数据库注释',
	a.TABLE_NAME AS '表名',
	b.table_comment AS '表注释',
	COLUMN_NAME AS '字段',
	COLUMN_TYPE AS '字段类型',
	COLUMN_COMMENT AS '备注' 
FROM
	information_schema.COLUMNS a,
	information_schema.TABLES b 
WHERE
	a.TABLE_SCHEMA = b.TABLE_SCHEMA 
	AND a.table_name = b.table_name
	AND b.TABLE_TYPE <> 'VIEW'
	AND a.TABLE_SCHEMA = 'asset'

查看表的现有占用容量

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 table_schema='asset' AND TABLE_TYPE <> 'VIEW'
order by table_rows desc, index_length desc;

查看表的列数

SELECT
	a.TABLE_NAME AS '表名',
	COUNT(*) AS '列数'
FROM
	information_schema.COLUMNS a,
	information_schema.TABLES b 
WHERE
	a.TABLE_SCHEMA = b.TABLE_SCHEMA 
	AND a.table_name = b.table_name
	AND b.TABLE_TYPE <> 'VIEW'
	AND a.TABLE_SCHEMA = 'asset'
GROUP BY a.TABLE_NAME