C C++ CMake CSS Cypress date-fns Django Django REST Framework ESLint Git GNU Make HTML htmx HTTP JavaScript MariaDB NumPy pandas PHP PostgreSQL Python PyTorch Qt SQLite Tailwind CSS

SHOW Your Way Around: Unveiling MariaDB Structure


  1. SQL Statements
    MariaDB uses SQL statements for various database operations. SHOW is one such statement categorized under retrieving information.

  2. Structure
    SHOW helps reveal the structure of your database. This includes details about:

    • Tables: You can use SHOW to see a list of tables, their structure (columns and data types), and even the specific SQL statement used to create a particular table (using SHOW CREATE TABLE).

  3. Objects
    MariaDB stores data in tables, but it also has other database objects like databases themselves, events, and functions. SHOW can be used to get information about these objects as well.

For example, the following SHOW statements provide structural information:

  • SHOW CREATE DATABASE database_name: This reveals the SQL statement used to create a particular database.

  • SHOW COLUMNS FROM table_name: This displays details about the columns within a specific table.


Listing All Databases

SHOW DATABASES;

This displays a list of all databases on the MariaDB server you're connected to.

Showing Table Details

SHOW COLUMNS FROM customers;

This displays information about the columns within the customers table, including their names, data types, and other properties.

Viewing Table Creation Statement

SHOW CREATE TABLE orders;

This reveals the exact SQL statement used to create the orders table, including column definitions and constraints.

Finding Privileges

SHOW GRANTS FOR current_user();

This displays the privileges granted to the current user for accessing and manipulating database objects.

Checking Engine Information

SHOW ENGINE INNODB STATUS;

This provides details about the status of the InnoDB storage engine, which is commonly used in MariaDB.


  1. Information_schema Database
    MariaDB has a built-in database named information_schema. This database contains tables that store information about your actual user databases. You can query these tables using standard SQL statements to retrieve structural details.

  2. EXPLAIN Statement
    While not exactly for structure exploration, the EXPLAIN statement can be helpful to understand how MariaDB will execute a specific SQL query you've written. This can indirectly reveal information about how the tables involved are structured (indexes used, etc.)

  3. Graphical User Interface (GUI) Tools
    If you're using a GUI administration tool for MariaDB, it might offer functionalities to view database structure visually. These tools often present tables, columns, and relationships in a user-friendly interface.

  • GUI Tools

    • Pros: User-friendly interface for visualizing database structure, good for beginners.
    • Cons: Functionality might be limited compared to SHOW statements or information_schema.

  • EXPLAIN Statement

    • Pros: Useful for understanding query execution plans and indirectly revealing table structure details.
    • Cons: Primarily focused on query execution, not a direct replacement for SHOW.

  • Information_schema

    • Pros: More powerful and flexible queries possible compared to basic SHOW statements.
    • Cons: Requires knowledge of the specific information_schema tables and their structure.