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 ;