查询mysql当前database下所有资源

问题

如何快速统计当前库在所有资源?

在 MySQL 中库下所有资源,通常指的是：某个数据库（库）中的所有对象，包括：

• 表（Tables）
• 视图（Views）
• 存储过程（Procedures）
• 函数（Functions）
• 触发器（Triggers）
• 事件（Events）

env

• mysql

1. 查看所有表

SHOW TABLES FROM your_database_name;

或者：

SELECT TABLE_NAME 
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = 'your_database_name';

---

2. 查看所有视图

SELECT TABLE_NAME 
FROM information_schema.VIEWS 
WHERE TABLE_SCHEMA = 'your_database_name';

---

3. 查看所有存储过程

SELECT ROUTINE_NAME 
FROM information_schema.ROUTINES 
WHERE ROUTINE_SCHEMA = 'your_database_name' 
  AND ROUTINE_TYPE = 'PROCEDURE';

---

4. 查看所有函数

SELECT ROUTINE_NAME 
FROM information_schema.ROUTINES 
WHERE ROUTINE_SCHEMA = 'your_database_name' 
  AND ROUTINE_TYPE = 'FUNCTION';

---

5. 查看所有触发器

SELECT TRIGGER_NAME, EVENT_OBJECT_TABLE 
FROM information_schema.TRIGGERS 
WHERE TRIGGER_SCHEMA = 'your_database_name';

---

6. 查看所有事件（Event Scheduler）

SELECT EVENT_NAME 
FROM information_schema.EVENTS 
WHERE EVENT_SCHEMA = 'your_database_name';

---

一键查看所有资源合并

如果你想快速一览某个库下有什么资源，可以用类似下面的分组查询：

SELECT 'TABLE' AS object_type, TABLE_NAME AS object_name 
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = 'your_database_name'
UNION
SELECT 'VIEW', TABLE_NAME 
FROM information_schema.VIEWS 
WHERE TABLE_SCHEMA = 'your_database_name'
UNION
SELECT 'PROCEDURE', ROUTINE_NAME 
FROM information_schema.ROUTINES 
WHERE ROUTINE_SCHEMA = 'your_database_name' AND ROUTINE_TYPE = 'PROCEDURE'
UNION
SELECT 'FUNCTION', ROUTINE_NAME 
FROM information_schema.ROUTINES 
WHERE ROUTINE_SCHEMA = 'your_database_name' AND ROUTINE_TYPE = 'FUNCTION'
UNION
SELECT 'TRIGGER', TRIGGER_NAME 
FROM information_schema.TRIGGERS 
WHERE TRIGGER_SCHEMA = 'your_database_name'
UNION
SELECT 'EVENT', EVENT_NAME 
FROM information_schema.EVENTS 
WHERE EVENT_SCHEMA = 'your_database_name';

---

如需查看某个库中资源的创建语句（DDL），可以使用：

SHOW CREATE TABLE your_table;
SHOW CREATE VIEW your_view;
SHOW CREATE PROCEDURE your_procedure;
SHOW CREATE FUNCTION your_function;
SHOW CREATE TRIGGER your_trigger;