ℹ️ MySQL Database Metadata & Info – Explore Schema, Tables, Columns & Stats

---

🧲 Introduction – Why View MySQL Metadata?

When managing a MySQL database, it’s essential to know what’s inside—what tables exist, what columns they have, what indexes are applied, and how the data is structured. MySQL provides rich metadata views and SHOW commands that let you query your schema like any other dataset.

By exploring this metadata, you can:

• Automate reporting
• Audit database structure
• Validate schema before migrations
• Debug or optimize performance

🎯 In this guide, you’ll learn:

• How to view database, table, and column metadata
• Use SHOW, DESCRIBE, and INFORMATION_SCHEMA
• Query engine-level and structural details
• Extract schema insights using SQL

---

📘 1. Viewing Databases and Tables

---

🔹 View All Databases

SHOW DATABASES;

Explanation:

Displays all databases accessible to the current user.

---

🔹 Select Current Database

SELECT DATABASE();

Explanation:

Returns the name of the currently selected database.

---

🔹 View All Tables in a Database

SHOW TABLES;

Explanation:

Lists all tables within the active database.

---

🔹 View Table Schema (DDL)

SHOW CREATE TABLE employees;

Explanation:

Outputs the CREATE TABLE SQL statement for the employees table, showing columns, indexes, and constraints.

---

🧾 2. View Columns & Data Types

---

🔹 View Column Details

DESCRIBE employees;

OR

SHOW COLUMNS FROM employees;

Explanation:

Returns each column’s name, data type, nullability, key status, default values, and extra info (e.g., auto_increment).

---

🔹 View Column Metadata via INFORMATION_SCHEMA

SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_KEY, COLUMN_DEFAULT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'company_db' AND TABLE_NAME = 'employees';

Explanation:

• Queries metadata from INFORMATION_SCHEMA.COLUMNS.
• Filters by the database (TABLE_SCHEMA) and table name.

---

🧩 3. Explore Indexes & Keys

---

🔹 Show Indexes on a Table

SHOW INDEXES FROM employees;

Explanation:

Displays details on indexes (including primary and unique keys) on the employees table.

---

🔹 Query Indexes via INFORMATION_SCHEMA.STATISTICS

SELECT INDEX_NAME, COLUMN_NAME, NON_UNIQUE
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'company_db' AND TABLE_NAME = 'employees';

Explanation:

Returns each index and its columns, including whether the index is unique (NON_UNIQUE = 0) or not.

---

📊 4. View Table Size & Row Stats

---

🔹 Estimate Table Size

SELECT 
  table_name, 
  ROUND((data_length + index_length) / 1024 / 1024, 2) AS size_mb
FROM INFORMATION_SCHEMA.TABLES
WHERE table_schema = 'company_db';

Explanation:

• Retrieves the size of each table in megabytes.
• Adds data and index size for accurate reporting.

---

🔹 Count Rows in a Table

SELECT table_name, table_rows
FROM INFORMATION_SCHEMA.TABLES
WHERE table_schema = 'company_db';

Explanation:

Returns estimated row counts per table. For precise counts, use SELECT COUNT(*) FROM table_name.

---

🧠 5. View Server & Session Metadata

---

🔹 MySQL Version Info

SELECT VERSION();

Explanation:

Returns the current MySQL server version (e.g., 8.0.36).

---

🔹 User & Host Info

SELECT USER(), CURRENT_USER();

Explanation:

• USER() shows the user/host from the client connection.
• CURRENT_USER() shows the authenticated user (may differ due to proxying).

---

🔹 List Active Sessions/Processes

SHOW PROCESSLIST;

Explanation:

Lists current queries, thread states, and connections to MySQL.

---

📘 Best Practices for Metadata Queries

✅ Tip	💡 Reason
Use INFORMATION_SCHEMA for automation	It’s SQL-based and works with joins & filters
Cache metadata in large systems	Reduces repetitive overhead
Use SHOW CREATE TABLE before migrations	Captures full schema including indexes and constraints
Monitor TABLE_ROWS for quick size check	Good for approximate analytics or alerts
Always qualify TABLE_SCHEMA in queries	Avoids conflicts across multiple databases

---

🚀 Real-World Use Cases

Scenario	Command/Query Example
Generate schema documentation	Query INFORMATION_SCHEMA.COLUMNS
Identify missing primary keys	Filter COLUMN_KEY != 'PRI' in COLUMNS table
Validate table structures	Use SHOW CREATE TABLE in CI/CD pipelines
Estimate database size	Use INFORMATION_SCHEMA.TABLES with data_length
Monitor connection usage	Use SHOW PROCESSLIST and SHOW STATUS

---

📌 Summary – Recap & Next Steps

MySQL provides rich metadata through both command-based inspection and queryable metadata views. These are critical for schema validation, documentation, DevOps audits, and real-time monitoring.

🔍 Key Takeaways

• Use SHOW commands for quick visibility (SHOW TABLES, SHOW INDEXES)
• Use INFORMATION_SCHEMA for advanced, queryable metadata
• Leverage SHOW CREATE TABLE to extract DDL with full structure
• Monitor row counts, table size, and server info for health checks

⚙️ Real-World Relevance

Whether you’re a developer validating schema, a DBA writing audit tools, or a DevOps engineer deploying migrations—MySQL metadata commands are essential for visibility and safety.

---

❓ FAQ – MySQL Metadata & Info

---

❓ What is INFORMATION_SCHEMA?

✅ It’s a special virtual database that stores metadata about databases, tables, columns, indexes, users, and more.

---

❓ How is SHOW TABLES different from querying INFORMATION_SCHEMA.TABLES?

• SHOW TABLES is quick and readable.
• INFORMATION_SCHEMA is SQL-queryable and filterable for automation.

---

❓ Does TABLE_ROWS always return an exact row count?

❌ No. It’s estimated—use SELECT COUNT(*) for accuracy, though it’s slower.

---

❓ How do I get a full table definition?

✅ Use:

SHOW CREATE TABLE table_name;

---

❓ How do I check which engine a table uses?

SELECT table_name, engine
FROM INFORMATION_SCHEMA.TABLES
WHERE table_schema = 'your_db';

---

Share Now :