sp_object MYSQL获取当前实例下指定对象与定义语句内容_MySQL

bitsCN.com

sp_object MYSQL获取当前实例下指定对象与定义语句内容

[sql]

DELIMITER $$    

USE `test`$$    

DROP PROCEDURE IF EXISTS `sp_object`$$    

CREATE PROCEDURE `sp_object` ( p_OBJECTNAME VARCHAR(255), p_DBNAME VARCHAR(255) )    

BEGIN    

/*    

作者：陈恩辉    

调用示例：    

CALL sp_object ( 'UpdateFactAdGroupDailyUsageByHourly','' );  

*/    

-- 过程与函数    

SELECT `type` AS __TYPE, db AS DBNAME ,`name` AS OBJECTNAME ,body AS  DEFINITION     

FROM mysql.proc a    

WHERE db LIKE  CONCAT(p_DBNAME,'%')     

    AND `name` LIKE  CONCAT(p_OBJECTNAME, '%')  -- AND `type` = 'PROCEDURE'    

-- 表    

UNION ALL     

SELECT 'TABLE' AS __TYPE, TABLE_SCHEMA,TABLE_NAME ,'' AS  DEFINITION     

FROM information_schema.TABLES a      

WHERE TABLE_SCHEMA LIKE  CONCAT(p_DBNAME,'%')    

    AND table_name LIKE CONCAT(p_OBJECTNAME,'%')    

-- 触发器    

UNION ALL     

SELECT 'TRIGGER' AS __TYPE ,TRIGGER_SCHEMA AS DBNAME ,TRIGGER_NAME ,ACTION_STATEMENT AS DEFINITION  FROM information_schema.`TRIGGERS` a    

WHERE TRIGGER_SCHEMA LIKE CONCAT(p_DBNAME,'%')      

    AND TRIGGER_NAME LIKE CONCAT(p_OBJECTNAME, '%')      

-- 视图    

UNION ALL     

SELECT 'VIEW' AS __TYPE ,TABLE_SCHEMA AS DBNAME,TABLE_NAME  AS `viewname`,VIEW_DEFINITION AS DEFINITION  FROM information_schema.`VIEWS` a    

WHERE TABLE_SCHEMA LIKE CONCAT(p_DBNAME,'%')      

    AND TABLE_NAME LIKE CONCAT(p_OBJECTNAME, '%')   

ORDER BY __TYPE ,DBNAME ;    

END$$     

DELIMITER ;