列出数据库中的所有表 [英] Listing all tables in a database

问题描述

是否有SQL命令将列出数据库中的所有表，并且它是与提供程序无关的（在MSSQLServer，Oracle和MySQL上有效）？

Is there a SQL command that will list all the tables in a database and which is provider independent (works on MSSQLServer, Oracle, MySQL)?

推荐答案

最接近的选项是查询表的 INFORMATION_SCHEMA 。

The closest option is to query the INFORMATION_SCHEMA for tables.

SELECT *
FROM INFORMATION_SCHEMA.Tables
WHERE table_schema = 'mydatabase';

INFORMATION_SCHEMA 是标准SQL的一部分，但并不是所有的供应商都支持它。据我所知，支持它的唯一的RDBMS供应商是：

The INFORMATION_SCHEMA is part of standard SQL, but not all vendors support it. As far as I know, the only RDBMS vendors that support it are:

• MySQL


• PostgreSQL


• Microsoft SQL Server 2000 / 2005 / 2008

• MySQL
• PostgreSQL
• Microsoft SQL Server 2000/2005/2008

某些品牌的数据库，例如Oracle，IBM DB2，Firebird，Derby等具有类似的目录视图，为您提供了一个界面，您可以在其中查询系统上的元数据。但是视图的名称，它们包含的列及其关系与 INFORMATION_SCHEMA 的ANSI SQL标准不匹配。换句话说，类似的信息是可用的，但是您用于获取该信息的查询是不同的。

Some brands of database, e.g. Oracle, IBM DB2, Firebird, Derby, etc. have similar "catalog" views that give you an interface where you can query metadata on the system. But the names of the views, the columns they contain, and their relationships don't match the ANSI SQL standard for INFORMATION_SCHEMA. In other words, similar information is available, but the query you would use to get that information is different.

（脚注：IBM DB2 UDB for System i中的目录视图不同于IBM DB2 UDB for Windows / * NIX中的目录视图 - 对UDB中的通用非常有用。）

(footnote: the catalog views in IBM DB2 UDB for System i are different from the catalog views in IBM DB2 UDB for Windows/*NIX -- so much for the Universal in UDB!)

品牌（例如SQLite）不提供任何可用的元数据接口。

Some other brands (e.g. SQLite) don't offer any queriable interface for metadata at all.

这篇关于列出数据库中的所有表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！