表:
select TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE,ENGINE from information_schema.tables where TABLE_SCHEMA not in ('performance_schema','information_schema','mysql');
视图:
select TABLE_SCHEMA,TABLE_NAME from information_schema.tables where table_type='view';
索引:
SELECT TABLE_SCHEMA,TABLE_NAME,INDEX_NAME,INDEX_TYPE FROM INFORMATION_SCHEMA.STATISTICS ;
存储过程:
select db,name from mysql.proc ;
函数:
select * from mysql.func
事件:
select db,name from mysql.event;
触发器:
select TRIGGER_SCHEMA,TRIGGER_NAME from information_schema.`TRIGGERS`;
约束
select TABLE_SCHEMA,TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE from information_schema.`TABLE_CONSTRAINTS`;
外键:
select CONSTRAINT_SCHEMA, TABLE_NAME, CONSTRAINT_NAME, REFERENCED_TABLE_NAME from `information_schema`.REFERENTIAL_CONSTRAINTS ;
判断所有的表是否有主键:
- SELECT
- TABLE_SCHEMA,table_name
- FROM
- information_schema.TABLES
- WHERE
- table_schema not in ('information_schema','performance_schema','sys','mysql') AND
- TABLE_NAME NOT IN (
- SELECT
- table_name
- FROM
- information_schema.table_constraints t
- JOIN information_schema.key_column_usage k USING (
- constraint_name,
- table_schema,
- table_name
- )
- WHERE
- t.constraint_type = 'PRIMARY KEY'
- AND t.table_schema not in ('information_schema','performance_schema','sys','mysql')
- );
统计 MySQL 数据库的数据量
- SELECT TABLE_SCHEMA,TABLE_NAME,ENGINE,TABLE_ROWS,AVG_ROW_LENGTH,concat(ROUND(DATA_LENGTH/1024/1024/1024,2),'G') "DATA_LENGTH" ,concat(ROUND(INDEX_LENGTH/1024/1024/1024,2),'G') "INDEX_LENGTH",concat(ROUND((INDEX_LENGTH/1024/1024/1024)+(DATA_LENGTH/1024/1024/1024),2),'G') "Total",TABLE_COLLATION,CREATE_TIME FROM
- INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'xxx' ORDER BY TABLE_ROWS DESC;
来源: http://www.bubuko.com/infodetail-2540316.html