MySQL 查詢儲存程序、函數、視圖、觸發器的完整指令集

儲存程序與函數

方法一：查詢 mysql.proc 系統表 (5.7 前推薦)

-- 儲存程序
SELECT `name` FROM mysql.proc WHERE db = 'your_db' AND `type` = 'PROCEDURE';

-- 函數
SELECT `name` FROM mysql.proc WHERE db = 'your_db' AND `type` = 'FUNCTION';

方法二：SHOW 指令 (直觀、跨版本)

SHOW PROCEDURE STATUS;
SHOW PROCEDURE STATUS WHERE db = 'servant_591up';

SHOW FUNCTION STATUS;
SHOW FUNCTION STATUS WHERE db = 'servant_591up';

查看建立原始碼

SHOW CREATE PROCEDURE proc_name;
SHOW CREATE FUNCTION func_name;

視圖

-- 所有視圖
SELECT * FROM information_schema.VIEWS WHERE TABLE_SCHEMA = 'your_db';

-- 特定視圖定義
SHOW CREATE VIEW view_name;

觸發器

方法一：SHOW TRIGGERS

SHOW TRIGGERS;
SHOW TRIGGERS FROM `your_db`;
SHOW TRIGGERS LIKE 'prefix_%';

方法二：查詢 information_schema.TRIGGERS

SELECT * FROM information_schema.TRIGGERS
WHERE TRIGGER_SCHEMA = 'your_db' AND TRIGGER_NAME = 'mytrigger'\G

資料表

SELECT * FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'your_db';\G

實用組合查詢範例

-- 列出資料庫所有程式物件
SELECT ROUTINE_NAME, ROUTINE_TYPE, DEFINER
FROM information_schema.ROUTINES
WHERE ROUTINE_SCHEMA = 'your_db'
ORDER BY ROUTINE_TYPE, ROUTINE_NAME;

-- 列出所有觸發器事件/時機
SELECT TRIGGER_NAME, EVENT_MANIPULATION, ACTION_TIMING, EVENT_OBJECT_TABLE
FROM information_schema.TRIGGERS
WHERE TRIGGER_SCHEMA = 'your_db';

權限提示

• 需具備 SELECT 權限於 mysql.proc 或 information_schema 相關表。
• SHOW CREATE PROCEDURE/FUNCTION 需為建立者或具備 SHOW_ROUTINE 權限 (8.0+)。