MySQL 表（表名含有大写字母的所有表）

表名含有大写字母的所有表：

select table_name 
from information_schema.tables 
where table_schema='databasename' and binary table_name REGEXP '[A-Z]';

有json格式的表：

 select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,DATA_TYPE from information_schema.columns where DATA_TYPE like 'json' and TABLE_SCHEMA not in ('sys','mysql','information_schema','performance_schema');

表大小：

select TABLE_SCHEMA,TABLE_TYPE,ENGINE,TABLE_NAME,ROUND((DATA_LENGTH+INDEX_LENGTH+DATA_FREE)/1024/1024/1024) TOTAL_GB 
from information_schema.tables 
where TABLE_SCHEMA='databasename' 
order by TOTAL_GB desc limit 10;

表数量：

select TABLE_SCHEMA,count(TABLE_NAME) from tables where TABLE_SCHEMA not in ('mysql','information_schema','performation_schema','sys') group by TABLE_SCHEMA;

分区数量：

select TABLE_SCHEMA,TABLE_NAME,count(PARTITION_NAME) from PARTITIONS where TABLE_SCHEMA not in ('mysql','information_schema','performation_schema','sys') group by TABLE_SCHEMA,TABLE_NAME;

查询大于1G无索引表：

SELECT TABLE_SCHEMA, TABLE_NAME, total_gb 
FROM (select T.TABLE_SCHEMA,T.TABLE_NAME,round(((data_length + index_length + data_free) / 1024 / 1024 / 1024),2) AS total_gb from TABLES T where T.TABLE_TYPE = 'BASE TABLE' AND T.TABLE_SCHEMA NOT IN ('SYS', 'MYSQL', 'INFORMATION_SCHEMA', 'PERFORMANCE_SCHEMA')) T 
WHERE total_gb > 1 
order by total_gb desc;

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！