How to show all the tables present in the database and server in MySQL using Python?

When working with MySQL databases in Python, you may need to retrieve a list of all tables present in a specific database or across the entire server. This can be accomplished using SQL commands like SHOW TABLES and querying the information_schema.

Syntax

To show tables present in a database −

SHOW TABLES

To show tables present across the server −

SELECT table_name FROM information_schema.tables

Required Steps

• Import MySQL connector

• Establish connection using connect()

• Create cursor object using cursor() method

• Execute the appropriate SQL query

• Fetch and display results

• Close the connection

Show Tables in Current Database

The SHOW TABLES command displays all tables in the currently connected database ?

import mysql.connector

# Establish database connection
db = mysql.connector.connect(
    host="localhost",
    user="root", 
    password="password",
    database="sample_db"
)

cursor = db.cursor()

# Execute SHOW TABLES command
cursor.execute("SHOW TABLES")

print("Tables in current database:")
for table_name in cursor:
    print(table_name[0])

# Close connection
cursor.close()
db.close()

Show All Tables in Server

To list tables from all databases on the server, query the information_schema.tables system table ?

import mysql.connector

# Establish connection (no specific database)
db = mysql.connector.connect(
    host="localhost",
    user="root",
    password="password"
)

cursor = db.cursor()

# Query information_schema for all tables
cursor.execute("SELECT table_schema, table_name FROM information_schema.tables WHERE table_type = 'BASE TABLE'")

print("All tables in server:")
for schema, table in cursor:
    print(f"{schema}.{table}")

# Close connection
cursor.close()
db.close()

Example Output

Tables in current database:
employees
students
products

All tables in server:
sample_db.employees
sample_db.students
inventory.products
hr_system.departments

Comparison

Conclusion

Use SHOW TABLES for listing tables in the current database and information_schema.tables for server-wide table discovery. Both approaches provide essential database introspection capabilities in MySQL Python applications.