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 ;
bitsCN.com