首頁 >資料庫 >mysql教程 >sp_object MYSQL获取当前实例下指定对象与定义语句内容_MySQL

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

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB
WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB原創
2016-06-01 13:34:22965瀏覽

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 ;    

 

bitsCN.com
陳述:
本文內容由網友自願投稿,版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容,請聯絡admin@php.cn