Sql 简明教程

SQL - Show Tables (Listing Tables)

你有时需要从数据库中检索表列表。这可以出于测试目的，识别在添加或删除任何表之前任何现有的表，或出于任何其他原因。本教程将讨论如何使用简单的 SQL 命令列出 MySQL、SQL Server 和 Oracle 中的所有表。

There are several instances when you need to retrieve a list of tables from your database. This could be done for testing purposes, to identify any existing tables before adding or removing any, or for any other reason. This tutorial will discuss how we can list down all the table in MySQL, SQL Server and Oracle using simple SQL commands.

MySQL - Listing Tables

你可以在 MySQL 中使用 SQL SHOW TABLES 语句列出选定数据库中所有可用的表。

You can use SQL SHOW TABLES statements in MySQL to list down all the tables available in a selected database.

Syntax

以下是在 MySQL 中列出所有表的语法 −

Following is the syntax to list all the tables in SQL in MySQL −

SHOW TABLES;

Example

以下是一个从 testDB 数据库中列出所有表的示例。

Following is an example which will list down all the tables from a testDB database.

USE testDB;

SHOW TABLES;

这将根据数据库中可用的表数量显示以下输出。

This will display the following output depending on the number of tables available in your database.

Tables_in_testDB

CALENDAR

CUSTOMERS

COMPANIES

SALARY

SQL Server - Listing Tables

SQL Server does not 提供 SQL Server 中的 SHOW TABLE 命令。相反，我们可以使用“SELECT”语句来检索数据库中有关表的的信息。在数据库中列出所有表时，我们有三个不同的命令与 SELECT 语句一同使用 -

SQL Server does not provide SHOW TABLE command in an SQL Server. Instead, we can use the "SELECT" statement to retrieve information about tables in a database. We have three different commands to use with the SELECT statement to list all the tables in a database −

  1. sys.tables

  2. information_schema.tables

  3. sysobjects

The SYS.TABLES View

以下是使用 SYS.TABLES 视图列出 SQL 中所有表的语法 -

Following is the syntax to list down all the tables in SQL using the SYS.TABLES view −

SELECT * FROM SYS.TABLES;

以下是以上查询的输出：

Following is the output of the above query −

name

object_id

principal_id

schema_id

CUSTOMER

4195065

NULL

1

ORDERS

68195293

NULL

1

COMPANIES

100195407

NULL

1

SALARY

2107154552

NULL

1

The INFORMATION_SCHEMA.TABLES View

以下是使用 INFORMATION_SCHEMA.TABLES 视图列出 SQL 中所有表的语法 -

Following is the syntax to list down all the tables in SQL using the INFORMATION_SCHEMA.TABLES view −

SELECT table_name, table_type FROM INFORMATION_SCHEMA.TABLES;

以下是以上查询的输出：

Following is the output of the above query −

table_name

table_type

CUSTOMER

BASE TABLE

ORDERS

BASE TABLE

COMPANIES

BASE TABLE

SALARY

BASE TABLE

The SYSOBJECTS View

您可以使用 SYSOBJECTS 视图来检索在 SQL Server 数据库中创建的所有对象的信息，包括存储过程、视图、系统表和用户自定义表。以下是使用 sysobjects 视图的基本语法 -

You can use SYSOBJECTS view to retrieve the information of all the objects created in SQL Server database, including stored procedures, views, system tables and user-defined tables. Following is the basic syntax of using sysobjects view −

SELECT name, id, xtype FROM sysobjects WHERE xtype = 'U';

这将生成以下结果：

This will produce following result −

name

id

xtype

CUSTOMER

4195065

U

ORDERS

68195293

U

COMPANIES

100195407

U

SALARY

2107154552

U

Oracle - Listing Tables

以下三个 SQL SELECT 语句可用于列出 Oracle 中可用的表。

There are following three SQL SELECT statements which you can use to list down the tables available in Oracle.

Listing ALL Tables

以下 SQL SELECT 语句将列出 Oracle 数据库中所有可用的表。

Following is the SQL SELECT statement which will list down all the available tables in an Oracle Database.

SELECT owner, table_name FROM ALL_TABLES

Listing DBA Tables

以下 SQL SELECT 语句将列出 Oracle 数据库中所有 DBA 相关表。

Following is the SQL SELECT statement which will list down all the DBA related tables in an Oracle Database.

SELECT owner, table_name FROM DBA_TABLES

Listing USER Tables

以下 SQL SELECT 语句将列出 Oracle 数据库中所有用户创建的表。

Following is the SQL SELECT statement which will list down all the USER created tables in an Oracle Database.

SELECT owner, table_name FROM USER_TABLES

Listing ALL Views

以下 SQL SELECT 语句将列出 Oracle 数据库中所有可用的视图。

Following is the SQL SELECT statement which will list down all the views available in an Oracle Database.

SELECT view_name FROM ALL_VIEWS;