关闭

[置顶] Mysql-找出实例下所有基表的真实行数

1057人阅读 评论(0) 收藏 举报
-- 验证触发器 
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 ;
1
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:110162次
    • 积分:1654
    • 等级:
    • 排名:千里之外
    • 原创:64篇
    • 转载:29篇
    • 译文:0篇
    • 评论:7条
    文章分类
    最新评论