如何在mysql命令行中看到存储过程或存储函数的列表,如show tables;或显示数据库;命令。


当前回答

如果要列出当前所选数据库的存储过程,请

SHOW PROCEDURE STATUS WHERE Db = DATABASE();

它将根据当前选择的数据库列出例程

更新 列出数据库中的函数

select * from information_schema.ROUTINES where ROUTINE_SCHEMA="YOUR DATABASE NAME" and ROUTINE_TYPE="FUNCTION";

要在数据库中列出例程/存储过程,

select * from information_schema.ROUTINES where ROUTINE_SCHEMA="YOUR DATABASE NAME" and ROUTINE_TYPE="PROCEDURE";

要列出数据库中的表,

select * from information_schema.TABLES WHERE TABLE_TYPE="BASE TABLE" AND TABLE_SCHEMA="YOUR DATABASE NAME";

要列出数据库中的视图,

方法1:

select * from information_schema.TABLES WHERE TABLE_TYPE="VIEW" AND TABLE_SCHEMA="YOUR DATABASE NAME";

方法2:

select * from information_schema.VIEWS WHERE TABLE_SCHEMA="YOUR DATABASE NAME";

其他回答

从MySQL 8.0开始,MySQL。Procs表已被删除。从这里的答案中运行任何使用这个表的命令都会产生一个错误,它说(相当有逻辑):

Table 'mysql.proc' doesn't exist

相反,要检索只有过程/函数名的列表,请使用:

SELECT specific_name FROM `information_schema`.`ROUTINES` WHERE routine_schema='<your_db_name>';

在我的例子中,我编辑了它,只显示过程而不显示函数:

SELECT specific_name FROM `information_schema`.`ROUTINES` WHERE routine_schema='<your_db_name>' AND routine_type='PROCEDURE';

列出所有数据库的用户过程和功能:

SELECT 
    `ROUTINE_SCHEMA` AS `database`
    ,`ROUTINE_TYPE` AS `type`
    ,`SPECIFIC_NAME` AS `name`
    ,`DTD_IDENTIFIER` AS `data_type`
FROM 
    `INFORMATION_SCHEMA`.`ROUTINES`
WHERE
  `definer` LIKE
  CONCAT('%', CONCAT((SUBSTRING_INDEX((SELECT user()), '@', 1)), '%'))
ORDER BY
    `database`
    ,`type`
    ,`name`
;

列出正在使用的数据库的用户程序和功能:

SELECT 
    `ROUTINE_SCHEMA` AS `database`
    ,`ROUTINE_TYPE` AS `type`
    ,`SPECIFIC_NAME` AS `name`
    ,`DTD_IDENTIFIER` AS `data_type`
FROM 
    `INFORMATION_SCHEMA`.`ROUTINES`
WHERE
  `definer` LIKE
  CONCAT('%', CONCAT((SUBSTRING_INDEX((SELECT user()), '@', 1)), '%'))
AND
   `ROUTINE_SCHEMA` = DATABASE()
ORDER BY
    `type`
    ,`name`
;

选择:

SELECT * FROM INFORMATION_SCHEMA.ROUTINES
SHOW PROCEDURE STATUS;
SHOW FUNCTION STATUS;

Praveenkumar_V帖子的一个变种:

SELECT `name` FROM mysql.proc WHERE db = 'dbname' AND `type` = 'PROCEDURE';
SELECT `name` FROM mysql.proc WHERE db = 'dbname' AND `type` = 'FUNCTION';

..这是因为我在做家务之后需要节省时间:

SELECT CONCAT(
     "GRANT EXECUTE ON PROCEDURE `"
    ,`name`
    ,"` TO username@'%'; -- "
    ,`comment`
)
FROM mysql.proc
WHERE db = 'dbname'
AND `type` = 'PROCEDURE';

SELECT CONCAT(
     "GRANT EXECUTE ON FUNCTION `"
    ,`name`
    ,"` TO username@'%'; -- "
    ,`comment`
)
FROM mysql.proc
WHERE db = 'dbname'
AND `type` = 'FUNCTION';