查询字段注释
查询表注释字段注释
SELECT
a.TABLE_SCHEMA 库名,
a.table_name 表名,
a.table_comment 表说明,
b.COLUMN_NAME 字段名,
b.column_comment 字段说明,
b.column_type 字段类型,
b.column_key 约束
FROM
information_schema.TABLES a
LEFT JOIN information_schema.COLUMNS b
ON a.table_name = b.TABLE_NAME and a.TABLE_SCHEMA = b.TABLE_SCHEMA
WHERE
a.TABLE_SCHEMA = 'qhdata_warehouse_north_stat' and a.TABLE_NAME LIKE 'dw_ytb_org_finance%'
ORDER BY
a.TABLE_SCHEMA,a.table_name
查询数据库名和表名 SELECT TABLE_SCHEMA,TABLE_NAME,TABLE_COMMENT FROM information_schema.`TABLES` WHERE TABLE_SCHEMA = 'db_lgdata_ioc' AND TABLE_NAME LIKE 'ud_lg_ioc_tech_inovate%'
select * from information_schema.ROUTINES where ROUTINE_SCHEMA='qhdata_theme' and ROUTINE_NAME like '%proc_db_mjfx_%'
查询存储过程
mysql如何查询所有的存储过程中是否使用到这张表 判断一个表在哪些存储过程中 select * from mysql.proc where db = 'db_lgstat_ioc' and type = 'PROCEDURE' and body like '%s_ls_tjj_qdsjgljlgqqrqymd%' 查询所有存储过程,存储过程备份 -- mysql5以下 select db, name, CONVERT (body USING utf8) from mysql.proc where db in ('test') and type = 'PROCEDURE'; -- mysql 8 select ROUTINE_SCHEMA,SPECIFIC_NAME,ROUTINE_DEFINITION from information_schema.ROUTINES where ROUTINE_TYPE='PROCEDURE' and ROUTINE_SCHEMA in('qhdata_market') ; mysql8.0写法 -- ROUTINE_SCHEMA 存储过程库名 -- ROUTINE_NAME 存储过程名 -- ROUTINE_DEFINITION 存储过程内容 select * from information_schema.ROUTINES where ROUTINE_TYPE='PROCEDURE' and ROUTINE_DEFINITION like '%dw_lg_ioc_gdzctz_jc%'
表大小
第二种:查询所有数据的大小 select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from information_schema.TABLES 第三种:查看指定数据库的大小,比如说:数据库apoyl select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from information_schema.TABLES where table_schema='apoyl'; 第四种:查看指定数据库的表的大小,比如说:数据库apoyl 中apoyl_test表 select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from information_schema.TABLES where table_schema='apoyl' and table_name='apoyl_test';
表容量
库的每个scheme select table_schema as '数据库', sum(table_rows) as '记录数', sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)', sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)' from information_schema.tables group by table_schema order by sum(data_length) desc, sum(index_length) desc 库的所有 select sum(记录数) ,sum(`数据容量(MB)`) ,sum(`索引容量(MB)`) from ( select table_schema as '数据库', sum(table_rows) as '记录数', sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)', sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)' from information_schema.tables group by table_schema order by sum(data_length) desc, sum(index_length) desc )b;
标签:1024,存储,sum,ROUTINE,mysql,table,备注,TABLE,schema From: https://www.cnblogs.com/shujuyr/p/18417913