SQL - Show Tables

SQL - Listing Database Tables

During database development and administration, you frequently need to see a list of available tables. This is essential for schema discovery, debugging, or planning migrations. This tutorial covers the standard, cross-platform method and the specific, often faster, commands for major database systems.

1. Introduction to Listing Tables

Every relational database management system (RDBMS) stores metadata about its own structure—including tables, columns, views, and indexes—in a set of system tables or views. Querying this metadata allows you to list the objects within your database. There are two primary approaches:

• The SQL Standard: Using the INFORMATION_SCHEMA, which is designed to be a consistent, portable interface across different SQL databases.
• Native Commands: Using commands or system views specific to the RDBMS you are using (e.g., SHOW TABLES in MySQL).

2. The Standard Method: INFORMATION_SCHEMA

The INFORMATION_SCHEMA is an ANSI SQL standard set of views that provides information about the metadata of a database. It is the most portable way to query for tables and is supported by PostgreSQL, MySQL, SQL Server, and others.

Syntax

SELECT table_name, table_type
FROM information_schema.tables
WHERE table_schema = 'your_database_name' -- Or 'public' for PostgreSQL
  AND table_type = 'BASE TABLE'; -- To exclude views

The table_schema filter is important to avoid listing system tables. The name of the default schema can vary (‘public’ in PostgreSQL, the database name in MySQL).

Example Output

table_name	table_type
employees	BASE TABLE
products	BASE TABLE
orders	BASE TABLE

3. Database-Specific Commands

While INFORMATION_SCHEMA is portable, most databases also offer their own native, often more concise, commands.

MySQL / MariaDB

The SHOW TABLES command is the most common way to list tables in a MySQL or MariaDB database.

-- First, select the database to work with
USE your_database_name;

-- Then, list the tables in it
SHOW TABLES;

You can also use a LIKE clause for pattern matching:

SHOW TABLES LIKE 'prod%'; -- Shows tables starting with 'prod'

PostgreSQL

When using the psql command-line client, the \dt command is a convenient shortcut.

\dt -- Lists tables in the current schema

To do this with a standard SQL query, you would use the information_schema as shown in the previous section.

SQL Server

SQL Server provides system views within the sys schema for detailed, server-specific metadata.

-- This view provides more SQL Server-specific details
SELECT name AS table_name, create_date, modify_date
FROM sys.tables;

A legacy view, sysobjects, also exists but is deprecated. You should prefer sys.tables or information_schema.tables for modern development.

Note: sysobjects is a backward-compatibility view and should not be used in new development work. Its xtype column used single-character codes (‘U’ for User Table, ‘V’ for View) to identify object types.

Oracle

Oracle uses a set of data dictionary views to store metadata. The most common ones for listing tables are:

• USER_TABLES: Lists all tables owned by the current user.
• ALL_TABLES: Lists all tables that the current user has access to.
• DBA_TABLES: Lists all tables in the entire database (requires higher privileges).

-- To see tables you own
SELECT table_name FROM USER_TABLES;

-- To see all tables you can access
SELECT owner, table_name FROM ALL_TABLES;

4. Best Practices for Portability

If you are writing an application or script that needs to work across different database systems, using the INFORMATION_SCHEMA is the most reliable and recommended approach. While native commands can be simpler to type, they lock your code into a specific RDBMS. For quick, interactive exploration via a command-line client, the native commands are perfectly acceptable and often more convenient.