mySQL查看存储过程、函数、视图、触发器

时间:2023-01-19 05:01:32

一、查看存储过程

1.show procedure status; //查看所有的
2.show create procedure proc_AllUser[proc_name]; 查看proc_AllUser这个存储过程的具体信息
3.select 'name' from mysql.proc where db = 'test' and 'type' = 'PROCEDURE';

e.g.
select * from mysql.proc where db = 'sfjc' and type = 'PROCEDURE'; //查看存储过程
select * from mysql.pro where db = 'sfjc' and type = 'function'; //查看函数

 
二、查看函数

1.show function status;  //对比下面的,查看的信息少,只是一些状态信息
2.select * from mysql.proc where db= 'test' and type = 'function'; //可以当成查看表

 
三、查看视图

1.select * from information_schema.VIEWS;

 
四、查看表

1.select * from information_schema.TABLES;

 
五、查看触发器

1.语法:SHOW TRIGGERS [FROM db_name] [LIKE expr]
实例:mysql>SHOW TRIGGERS \G; //查看触发器
(实战中加了'\G':在客户端下没用,在cmd中操作命令可行,用于纵向显示)
e.g.
show triggers from sfjc where definer like 'bghc%'; //显示definer中以bghc开头的
show create trigger sfjc_dj_djlxr_before_update[trigger_name];

2.对INFORMATION_SCHEMA数据库中的TRIGGERS表查询
mysql>SELECT * FROM triggers T WHERE trigger_name=”mytrigger” \G
select * from triggers where trigger_name like 'sfjc_dj%';

 

 

http://weidongke123-126-com.iteye.com/blog/1675375 (可看)

http://blog.sina.com.cn/s/blog_6d187d2701019uew.html