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;

Try these queries in phpMyAdmin after creating the sample table. The returned names depend on your database and access permissions.

What you see depends on permissions. MySQL normally shows only databases and objects the current account is allowed to see.

Quick workflow: Run CREATE TABLE in a test schema, use SHOW TABLES; to confirm the object appears, then use SHOW CREATE TABLE student; to inspect its definition. No schema is changed by SHOW commands.

On this page — Show page sectionsHide page sections

SHOW DATABASES

List databases visible to the current MySQL account:

SHOW DATABASES;

The result contains one database name per row.

Do not assume SHOW DATABASES lists every database on the server. Visibility depends on the privileges assigned to the current MySQL account.

Check the Current Database

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;
Applications usually select the database in the connection DSN, so a separate USE statement is often unnecessary in PHP code.

SHOW TABLES

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

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

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

SHOW TABLES FROM sql_tutorial
LIKE 'analytics%';
In LIKE patterns, underscore _ is a one-character wildcard. If you specifically need a literal underscore in a more complex pattern, escape it according to the SQL mode and pattern syntax being used.

SHOW FULL TABLES: Tables vs Views

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

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

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

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

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

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

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,
        'UTF-8'
    );

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

echo '</select>';
?>

See PHP PDO database connection.

PDO: Display Table Names

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,
        'UTF-8'
    )
    .'<br>';
}
?>

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

Dynamic Database Names and Safety

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.

Do not concatenate an arbitrary request value into SHOW TABLES FROM, DROP TABLE, ALTER TABLE, or other identifier positions. Prepared statements protect data values, not arbitrary database/table/column names.

Permissions and Visibility

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

Running SHOW TABLES without a selected database

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

Assuming SHOW DATABASES displays every database

Results depend on the current account's privileges.

Assuming SHOW TABLES contains only base tables

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

Depending on the dynamic SHOW TABLES column name

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

_ 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

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

Frequently Asked Questions

Q1: How do I list databases in MySQL?

Use SHOW DATABASES. The databases shown depend on the privileges of the current MySQL account.

Q2: How do I list tables in the current database?

Use SHOW TABLES after selecting a database through the connection or USE statement.

Q3: How do I list tables from another database?

Use SHOW TABLES FROM database_name if the current account has permission to see that database.

Q4: How do I filter table names?

Use SHOW TABLES LIKE 'prefix%' for a simple name filter, or query INFORMATION_SCHEMA.TABLES for more flexible filtering and sorting.

Q5: How do I tell a table from a view?

Use SHOW FULL TABLES or inspect TABLE_TYPE in INFORMATION_SCHEMA.TABLES.

Q6: How do I see the SQL definition of an existing table?

Use SHOW CREATE TABLE table_name.

Q7: Can I bind a database name with a PDO placeholder?

No. Placeholders bind data values, not SQL identifiers such as database or table names. Use fixed or strictly allowlisted identifiers.


Date Ranges Primary Key CREATE TABLE SHOW CREATE TABLE

Back to top ↑




Subscribe to our YouTube Channel here



plus2net.com




SQL Video Tutorials










✖
We use cookies to improve your browsing experience. . Learn more
HTML MySQL PHP JavaScript ASP Photoshop Articles Contact us
© 2000-2026 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer