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

最新推荐文章于 2025-11-27 13:57:04 发布

最新推荐文章于 2025-11-27 13:57:04 发布 · 102 阅读

文章标签：

#数据库

本文介绍了一个MySQL存储过程，用于查询指定数据库中包含特定名称前缀的过程、函数、表、触发器及视图的相关信息。

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 ;