MySQL SHOW DATABASES and SHOW TABLES

Use SHOW DATABASES to list databases visible to the current MySQL account, and SHOW TABLES to list tables and views visible in the selected database.

SHOW DATABASES;

SHOW TABLES;

SHOW DATABASES Top ↑

List databases visible to the current MySQL account:

SHOW DATABASES;

The result contains one database name per row.

Check the Current Database Top ↑

To see which database is currently selected:

SELECT DATABASE() AS current_database;

If no database is selected, DATABASE() returns NULL.

You can select a database in SQL with:

USE sql_tutorial;

SHOW TABLES Top ↑

List tables and views in the currently selected database:

SHOW TABLES;

If no database has been selected, MySQL cannot know which database's tables you want to list.

SHOW TABLES FROM Another Database Top ↑

You can name the database explicitly:

SHOW TABLES FROM sql_tutorial;

This is useful when the connection can access several databases and you do not want to change the current database.

Filter Table Names with LIKE Top ↑

Use LIKE to filter table names.

For example, list names beginning with analytics:

SHOW TABLES LIKE 'analytics%';

The percent sign matches any sequence of characters.

Filter tables in a named database Top ↑

SHOW TABLES FROM sql_tutorial
LIKE 'analytics%';

SHOW FULL TABLES: Tables vs Views Top ↑

Plain SHOW TABLES lists object names but does not tell you whether each object is a base table or a view. Use:

SHOW FULL TABLES;

The result includes a second column describing the object type, such as BASE TABLE or VIEW.

Only base tables Top ↑

For precise filtering and portable column names, INFORMATION_SCHEMA.TABLES is usually clearer:

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_NAME;

SHOW CREATE TABLE Top ↑

To inspect the SQL definition MySQL stores for a table:

SHOW CREATE TABLE student;

This is useful for reviewing:

• column definitions,
• primary and secondary indexes,
• foreign keys,
• storage engine,
• character set and collation,
• table options.

See Copy Table and SHOW CREATE TABLE.

INFORMATION_SCHEMA.TABLES Top ↑

INFORMATION_SCHEMA.TABLES provides structured metadata and is useful when you need filtering, sorting, or additional table properties.

SELECT TABLE_NAME,
       TABLE_TYPE,
       ENGINE
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'sql_tutorial'
ORDER BY TABLE_NAME;

Use the currently selected database Top ↑

SELECT TABLE_NAME,
       TABLE_TYPE
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = DATABASE()
ORDER BY TABLE_NAME;

Using DATABASE() avoids hard-coding the database name when the current connection already selected it.

Order Table Names Top ↑

The original tutorial used INFORMATION_SCHEMA.TABLES to order names. That remains a good approach:

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'sql_tutorial'
ORDER BY TABLE_NAME ASC;

You can combine sorting with a name filter:

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'sql_tutorial'
  AND TABLE_NAME LIKE 'analytics%'
ORDER BY TABLE_NAME ASC;

See ORDER BY.

PDO: Display Databases in a Select Box Top ↑

The original page demonstrated a dropdown containing database names. Here is the same idea with safe HTML output:

<?php
require "config.php";

$stmt=$dbo->query("SHOW DATABASES");

echo '<select name="database">';

while($row=$stmt->fetch(PDO::FETCH_NUM)){
    $db=htmlspecialchars(
        (string)$row[0],
        ENT_QUOTES,
        'Windows-1252'
    );

    echo '<option value="'
        .$db
        .'">'
        .$db
        .'</option>';
}

echo '</select>';

See PHP PDO database connection.

PDO: Display Table Names Top ↑

For the database already selected by the PDO connection:

<?php
require "config.php";

$stmt=$dbo->query("SHOW TABLES");

while($row=$stmt->fetch(PDO::FETCH_NUM)){
    echo htmlspecialchars(
        (string)$row[0],
        ENT_QUOTES,
        'Windows-1252'
    )
    .'<br>';
}

The query contains no external data, so query() is appropriate.

Dynamic Database Names and Safety Top ↑

Database and table names are SQL identifiers, not normal data values. PDO placeholders cannot be used like this:

-- This does not parameterize an SQL identifier
SHOW TABLES FROM :database

If an application lets a user choose from databases returned by SHOW DATABASES, validate the submitted name against a server-side allowlist before using it as an identifier.

Permissions and Visibility Top ↑

The output of metadata commands depends on MySQL privileges.

• SHOW DATABASES may show only databases for which the account has privileges.
• SHOW TABLES shows objects visible to the current account in the selected database.
• SHOW CREATE TABLE also requires sufficient access to inspect the table definition.
• Applications should generally use the minimum database privileges needed for their work.

If an expected table is absent from the result, first confirm the selected database and the account's privileges before assuming the table does not exist.

Common SHOW TABLES Mistakes Top ↑

Running SHOW TABLES without a selected database Top ↑

Select a database in the connection or use SHOW TABLES FROM database_name.

Assuming SHOW DATABASES displays every database Top ↑

Results depend on the current account's privileges.

Assuming SHOW TABLES contains only base tables Top ↑

Views can also appear. Use SHOW FULL TABLES or INFORMATION_SCHEMA.TABLES when object type matters.

Depending on the dynamic SHOW TABLES column name Top ↑

The result column is named according to the database, for example Tables_in_sql_tutorial. When you need portable filtering and sorting, INFORMATION_SCHEMA provides stable column names such as TABLE_NAME.

Forgetting that underscore is a LIKE wildcard Top ↑

_ means one character in a LIKE pattern. Use a simpler prefix such as 'analytics%' when that is the real requirement, or escape a literal underscore deliberately.

Using untrusted database names directly in SQL Top ↑

Identifiers cannot be safely bound with normal PDO value placeholders. Use fixed names or a strict allowlist.

Related Tutorials Top ↑

Frequently Asked Questions Top ↑

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author   Watch on YouTube

Connect: