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