在数据库的日常管理、系统开发、代码生成或数据迁移等众多场景中，我们常常需要快速了解一个数据库实例中包含哪些数据表，通过 SQL 查询来获取所有表的列表，是一项基础且重要的操作，虽然 SQL 是一种标准化的查询语言，但不同数据库管理系统（DBMS）在实现这一功能时，提供了略微不同的方式，本文将详细介绍在主流数据库中如何查询所有表,并探讨如何获取更丰富的表信息。
使用 INFORMATION_SCHEMA：标准化的方法
INFORMATION_SCHEMA 是一套提供数据库元数据信息的视图集合，它是 SQL 标准的一部分，因此在许多主流数据库中都得到了支持，通过查询 INFORMATION_SCHEMA.TABLES 视图,可以以一种相对统一的方式获取表的信息。
这种方法的最大优点是可移植性强，一旦你学会了在一种数据库中使用它,就可以很容易地将查询语句应用到其他支持该标准的数据库上。
基本查询语句：
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'your_database_name';
这里的 your_database_name 需要替换为你实际的数据库名称，此查询会返回指定数据库中所有表和视图的名称，如果你只想获取基础表,可以添加条件：
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'your_database_name' AND TABLE_TYPE = 'BASE TABLE';
各大数据库的特定查询方法
尽管 INFORMATION_SCHEMA 是标准，但许多数据库也提供了自己更快捷、更习惯的查询方式。
MySQL
在 MySQL 中，除了使用 INFORMATION_SCHEMA，最简单快捷的方式是使用 SHOW 命令。
使用 SHOW TABLES 命令
SHOW TABLES;
执行此命令前，需要先用 USE your_database_name; 选中目标数据库,这个命令会以一列的形式直接展示当前数据库下的所有表。
查询 INFORMATION_SCHEMA
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'your_database_name' AND TABLE_TYPE = 'BASE TABLE';
这种方法更灵活,便于与其他查询结合或进行更复杂的筛选。
PostgreSQL
PostgreSQL 同样支持 INFORMATION_SCHEMA,这是推荐的标准做法。
查询 information_schema.tables
SELECT table_name FROM information_schema.tables WHERE table_schema = 'public' AND table_type = 'BASE TABLE';
在 PostgreSQL 中，默认的用户创建的表通常位于 public 模式下。
使用 psql 的元命令（非 SQL）
如果你在使用 psql 命令行工具，可以使用 \dt 命令,它能非常友好地列出当前模式下的所有表。
\dtSQL Server
在 SQL Server (T-SQL) 中,有多种方式可以查询表列表。
查询系统视图 sys.tables
这是最常用和最高效的方法之一。
SELECT name FROM sys.tables;
此查询会返回当前数据库下所有用户表的名称。
查询 INFORMATION_SCHEMA.TABLES
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE';
Oracle
Oracle 数据库通过数据字典视图来提供元数据信息。
查询 ALL_TABLES
ALL_TABLES 视图包含了当前用户可以访问的所有表。
SELECT TABLE_NAME FROM ALL_TABLES;
查询 USER_TABLES
USER_TABLES 视图仅包含当前用户所拥有的表。
SELECT TABLE_NAME FROM USER_TABLES;
SQLite
SQLite 使用一个特殊的系统表 sqlite_master 来存储数据库的模式信息。
查询 sqlite_master 表
SELECT name FROM sqlite_master WHERE type = 'table';
这条查询会返回数据库中所有表的名称。sqlite_master 表还包含了索引、视图等其他对象的信息，因此需要通过 WHERE type = 'table' 进行筛选。
获取更详细的表信息
我们不仅需要表名，还可能需要知道表所属的模式（Schema）、表类型（是表还是视图）等信息。INFORMATION_SCHEMA.TABLES 视图提供了丰富的列来满足这些需求。
以下是一些常用的列：
| 列名 | 描述 |
|---|---|
TABLE_CATALOG |
表所属的目录（通常是数据库名） |
TABLE_SCHEMA |
表所属的模式或数据库 |
TABLE_NAME |
表的名称 |
TABLE_TYPE |
对象类型，如 'BASE TABLE', 'VIEW' |
ENGINE |
存储引擎（MySQL 特有） |
通过选择这些列,可以得到一个更全面的表清单：
SELECT
TABLE_SCHEMA,
TABLE_NAME,
TABLE_TYPE
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_SCHEMA NOT IN ('information_schema', 'performance_schema', 'mysql', 'sys');
这个例子（在 MySQL 中）排除了系统自带的数据库,专注于用户创建的表和视图。
注意事项与最佳实践
- 权限问题：执行这些查询通常需要用户拥有至少对系统目录或
INFORMATION_SCHEMA视图的SELECT权限，在某些严格的环境中,普通用户可能无权查看所有表。 - 性能考量：对于包含成百上千个表的大型数据库，查询
INFORMATION_SCHEMA可能会比查询特定数据库的系统视图稍慢，因为它需要进行更多的关联和标准化处理，但在绝大多数情况下,这种性能差异可以忽略不计。 - 选择合适的方法：如果追求代码的跨数据库兼容性，
INFORMATION_SCHEMA是首选，如果在一个固定的数据库环境中工作，使用其原生的系统视图（如sys.tables）或命令（如SHOW TABLES）通常更直接、高效。
相关问答 FAQs
问题1：INFORMATION_SCHEMA 和数据库特定的命令（如 SHOW TABLES）有什么区别，我应该优先使用哪个？
解答： 主要区别在于标准化与便利性。INFORMATION_SCHEMA 是 SQL 标准的一部分，提供了跨数据库平台的可移植性，使得相同的查询逻辑可以在 MySQL, PostgreSQL, SQL Server 等多种数据库上运行，但它的语法可能稍显冗长，而数据库特定的命令，如 MySQL 的 SHOW TABLES; 或 SQL Server 的 SELECT name FROM sys.tables;，通常是该数据库社区中最常用、最快捷的方式，语法简洁，且可能针对该系统做了性能优化,但缺点是无法移植到其他类型的数据库。
选择建议：如果你正在开发一个需要支持多种数据库后端的应用程序，或者你是一名经常在不同数据库系统间切换的 DBA，应优先使用 INFORMATION_SCHEMA，如果你长期在单一的数据库环境中工作，追求最高的效率和简洁性,那么使用该数据库的原生命令或系统视图是更好的选择。
问题2：如何查询特定模式或用户下的表？
解答： 查询特定模式或用户下的表，通常是在查询语句中添加一个 WHERE 过滤条件，具体实现方式因数据库而异,但核心思想一致。
- 对于
INFORMATION_SCHEMA：大多数实现中，可以使用TABLE_SCHEMA或TABLE_CATALOG列来筛选，在 MySQL 中查询名为prod_db的数据库下的所有表：SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'prod_db';
- 对于数据库特定视图：
- 在 Oracle 中，可以使用
USER_TABLES查看当前用户的表，或从ALL_TABLES中筛选OWNER字段：SELECT TABLE_NAME FROM ALL_TABLES WHERE OWNER = 'SCOTT';
- 在 PostgreSQL 中，可以筛选
table_schema：SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';
- 在 SQL Server 中，如果不同模式的表名不同，可以直接查询
sys.objects或sys.tables并结合sys.schemas：SELECT t.name FROM sys.tables t INNER JOIN sys.schemas s ON t.schema_id = s.schema_id WHERE s.name = 'dbo';
- 在 Oracle 中，可以使用
通过结合 WHERE 子句,你可以精确地定位到任何你感兴趣的特定范围下的表。