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

页面导航:首页 > 数据库 > Mysql > sp_object MYSQL获取当前实例下指定对象与定义语句

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

来源: 作者: 时间:2016-02-21 09:46 【

sp_object MYSQL获取当前实例下指定对象与定义语句内容[sql]DELIMITER $$ USE `test`$$ DROP PROCEDURE IF EXISTS `sp_object`$$ CREATE PROCEDURE `sp_object` ( p_OBJECTNAME VARC...
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 .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 ;    
 
Tags:

文章评论

最 近 更 新
热 点 排 行
Js与CSS工具
代码转换工具

<