Mysql-找出实例下所有基表的真实行数

-- 验证触发器 
SELECT t.TRIGGER_SCHEMA, t.TRIGGER_NAME,  
MD5(  
CONCAT(  
 IFNULL(TRIGGER_CATALOG,''),IFNULL(TRIGGER_SCHEMA,''),IFNULL(TRIGGER_NAME,''),IFNULL(EVENT_MANIPULATION,''),IFNULL(EVENT_OBJECT_CATALOG,''),IFNULL(EVENT_OBJECT_SCHEMA,''),IFNULL(EVENT_OBJECT_TABLE,''),IFNULL( ACTION_ORDER,''),IFNULL(ACTION_CONDITION,''),IFNULL(ACTION_STATEMENT,''),IFNULL(ACTION_ORIENTATION,''),IFNULL(ACTION_TIMING,''),IFNULL(ACTION_REFERENCE_OLD_TABLE,''),IFNULL(ACTION_REFERENCE_NEW_TABLE,''),IFNULL(ACTION_REFERENCE_OLD_ROW,''),IFNULL(ACTION_REFERENCE_NEW_ROW,''),IFNULL(CREATED,''),IFNULL(SQL_MODE,''),IFNULL(DEFINER,''),IFNULL(CHARACTER_SET_CLIENT,''),IFNULL(COLLATION_CONNECTION,''),IFNULL(DATABASE_COLLATION,'')   
)) AS md5_  
FROM information_schema.TRIGGERS  t; 
  
  
-- 验证存储过程与函数
SELECT  CONCAT(p.db,'.',p.name) AS object ,TYPE ,MD5(body) md5_value FROM mysql.proc AS p   
WHERE db NOT LIKE '%-%'  
ORDER BY 2 ,1 ; <span style="font-family: Arial, Helvetica, sans-serif;">
-- 验证event  
SELECT CONCAT(A.DB,'.',A.NAME) AS EVENT ,MD5(BODY) MD5_ ,A.definer FROM mysql.event  A ; 


-- 验证表数据 ,拼出来的语句,稍微修改一下即可
SELECT CONCAT(' select  ''', t.TABLE_SCHEMA ,''' as db ,''',t.TABLE_NAME,''' as tbname, count(1) as row_count from `', t.TABLE_SCHEMA ,'`.`',t.TABLE_NAME,'` union all ')  AS Sql_Exec    
FROM information_schema.TABLES AS t    
LEFT JOIN ( SELECT * FROM information_schema.TABLES   
        ORDER BY TABLE_ROWS DESC LIMIT 13 ) d ON d.TABLE_SCHEMA = t.TABLE_SCHEMA AND d.TABLE_NAME = t.TABLE_NAME  
WHERE t.TABLE_TYPE = 'BASE TABLE'   
AND t.TABLE_SCHEMA NOT LIKE  '%-%'   
AND d.TABLE_SCHEMA IS NULL   
ORDER BY Sql_Exec ; 
-- 找出所有的自增加键的最大值
SELECT CONCAT('select ''',c.TABLE_SCHEMA,'.',c.TABLE_NAME,''' _table_name, max(a.', COLUMN_NAME,') _MaxValue from `',c.TABLE_SCHEMA ,'`.`', c.TABLE_NAME,'` a  union all ') AS auto_increment_MaxValue
-- ,c.* 
FROM   information_schema.COLUMNS c -- on t.TABLE_SCHEMA = c.TABLE_SCHEMA and t.TABLE_NAME = c.TABLE_NAME 
WHERE c.EXTRA = 'auto_increment'
AND c.TABLE_SCHEMA NOT LIKE '%-%'
ORDER BY c.TABLE_SCHEMA,c.TABLE_NAME ;
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值