查看MySQL中的对象
表：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_NAMEfrom information_schema.tables where table_type='view';
索引：
SELECT TABLE_SCHEMA,TABLE_NAME,INDEX_NAME,INDEX_TYPE FROM INFORMATION_SCHEMA.STATISTICS;
存储过程：
selectdb,namefrommysql.proc ;
函数：
select *frommysql.func
事件：
selectdb,namefrommysql.event;
触发器：
select TRIGGER_SCHEMA,TRIGGER_NAME frominformation_schema.`TRIGGERS`;
约束
select TABLE_SCHEMA,TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE frominformation_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数据库的数据量
SELECTTABLE_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;
页:
[1]