How to view table schema in MySQL or MariaDB

Table schema inspection in MySQL or MariaDB prevents application breakage caused by silent type mismatches, unexpected NULL behavior, missing indexes. How to view table schema in MySQL or MariaDB.

Both servers expose schema metadata through SHOW statements, DESCRIBE shortcuts, and the information_schema database. SHOW FULL COLUMNS returns column-level details, while SHOW CREATE TABLE emits the canonical DDL that includes indexes, constraints, table options, charset, collation.

Access permissions influence what schema can be read, especially for views and routines, and output fields can vary slightly by version between MySQL and MariaDB. Quoting identifiers with backticks avoids surprises when names collide with reserved words, and triggers are stored separately from the table definition, requiring a separate lookup when behavior seems “magical”.

1. Connect to the server using the mysql client.

   $ mysql -h db.example.net -u appuser -p
   Enter password: ********
   Welcome to the MySQL monitor.  Commands end with ; or \g.
   Your MySQL connection id is 194
   Server version: 8.0.36 MySQL Community Server - GPL
   ##### snipped #####
   mysql>

2. Select the database that contains the table.

   mysql> USE appdb;
   Database changed

3. Confirm the current database context.

   mysql> SELECT DATABASE();
   +------------+
   | DATABASE() |
   +------------+
   | appdb      |
   +------------+
   1 row in set (0.00 sec)

4. Confirm the table name exists in the selected database.

   mysql> SHOW TABLES LIKE 'orders';
   +-----------------------+
   | Tables_in_appdb (orders) |
   +-----------------------+
   | orders                |
   +-----------------------+
   1 row in set (0.00 sec)

   Use backticks for awkward names, e.g., `order`, `user-profile`.

5. Show engine, collation, row format, creation options for the table.

   mysql> SHOW TABLE STATUS LIKE 'orders'\G
   *************************** 1. row ***************************
              Name: orders
            Engine: InnoDB
         Row_format: Dynamic
          Collation: utf8mb4_unicode_ci
              Rows: 1024
     Avg_row_length: 192
        Data_length: 196608
   ##### snipped #####

6. Display column names, types, nullability, keys, defaults, extra attributes.

   mysql> SHOW FULL COLUMNS FROM orders;
   +-------------+---------------------+--------------------+------+-----+----------------------+----------------+-----------------------------------------+---------+
   | Field       | Type                | Collation          | Null | Key | Default              | Extra          | Privileges                              | Comment |
   +-------------+---------------------+--------------------+------+-----+----------------------+----------------+-----------------------------------------+---------+
   | id          | bigint unsigned     | NULL               | NO   | PRI | NULL                 | auto_increment | select,insert,update,references         |         |
   | customer_id | bigint unsigned     | NULL               | NO   | MUL | NULL                 |                | select,insert,update,references         |         |
   | status      | varchar(20)         | utf8mb4_unicode_ci | NO   |     | pending              |                | select,insert,update,references         |         |
   | created_at  | datetime(6)         | NULL               | NO   |     | CURRENT_TIMESTAMP(6) |                | select,insert,update,references         |         |
   +-------------+---------------------+--------------------+------+-----+----------------------+----------------+-----------------------------------------+---------+
   4 rows in set (0.00 sec)

   DESCRIBE and SHOW COLUMNS are shorter aliases, but SHOW FULL COLUMNS exposes collation, privileges, comments.

7. Show the full CREATE TABLE statement for the table.

   mysql> SHOW CREATE TABLE orders\G
   *************************** 1. row ***************************
          Table: orders
   Create Table: CREATE TABLE `orders` (
     `id` bigint unsigned NOT NULL AUTO_INCREMENT,
     `customer_id` bigint unsigned NOT NULL,
     `status` varchar(20) NOT NULL DEFAULT 'pending',
     `created_at` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
     PRIMARY KEY (`id`),
     KEY `idx_orders_customer_id` (`customer_id`),
     CONSTRAINT `fk_orders_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

   SHOW CREATE TABLE is the closest thing to “source of truth” for indexes, constraints, table options, partitioning; triggers are not included.

8. List indexes with uniqueness, column order, index type.

   mysql> SHOW INDEX FROM orders;
   +--------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
   | Table  | Non_unique | Key_name               | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type  | Comment | Index_comment |
   +--------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
   | orders |          0 | PRIMARY                |            1 | id          | A         |        1024 | NULL     | NULL   |      | BTREE       |         |               |
   | orders |          1 | idx_orders_customer_id |            1 | customer_id | A         |         256 | NULL     | NULL   |      | BTREE       |         |               |
   +--------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
   2 rows in set (0.01 sec)

   Some MySQL versions add Visible, Expression columns, while MariaDB may omit them.

9. List foreign-key relationships for the table from information_schema.

   mysql> SELECT
       ->   kcu.CONSTRAINT_NAME,
       ->   kcu.COLUMN_NAME,
       ->   kcu.REFERENCED_TABLE_NAME,
       ->   kcu.REFERENCED_COLUMN_NAME,
       ->   rc.UPDATE_RULE,
       ->   rc.DELETE_RULE
       -> FROM information_schema.KEY_COLUMN_USAGE AS kcu
       -> JOIN information_schema.REFERENTIAL_CONSTRAINTS AS rc
       ->   ON rc.CONSTRAINT_SCHEMA = kcu.CONSTRAINT_SCHEMA
       ->  AND rc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
       -> WHERE kcu.TABLE_SCHEMA = DATABASE()
       ->   AND kcu.TABLE_NAME = 'orders'
       ->   AND kcu.REFERENCED_TABLE_NAME IS NOT NULL
       -> ORDER BY kcu.CONSTRAINT_NAME, kcu.ORDINAL_POSITION;
   +-------------------+-------------+------------------------+------------------------+-------------+-------------+
   | CONSTRAINT_NAME   | COLUMN_NAME | REFERENCED_TABLE_NAME  | REFERENCED_COLUMN_NAME | UPDATE_RULE | DELETE_RULE |
   +-------------------+-------------+------------------------+------------------------+-------------+-------------+
   | fk_orders_customer | customer_id | customers              | id                     | CASCADE     | RESTRICT    |
   +-------------------+-------------+------------------------+------------------------+-------------+-------------+
   1 row in set (0.00 sec)

   Foreign keys exist only on engines that support them (typically InnoDB), and absent rows usually means no FK constraints are defined.

10. Query information_schema.COLUMNS when precision, defaults, generated columns need explicit metadata.

    mysql> SELECT
        ->   ORDINAL_POSITION,
        ->   COLUMN_NAME,
        ->   COLUMN_TYPE,
        ->   IS_NULLABLE,
        ->   COLUMN_DEFAULT,
        ->   EXTRA,
        ->   COLUMN_KEY,
        ->   CHARACTER_SET_NAME,
        ->   COLLATION_NAME
        -> FROM information_schema.COLUMNS
        -> WHERE TABLE_SCHEMA = DATABASE()
        ->   AND TABLE_NAME = 'orders'
        -> ORDER BY ORDINAL_POSITION;
    +-----------------+-------------+---------------------+-------------+----------------------+----------------+------------+--------------------+--------------------+
    | ORDINAL_POSITION | COLUMN_NAME | COLUMN_TYPE         | IS_NULLABLE | COLUMN_DEFAULT       | EXTRA          | COLUMN_KEY | CHARACTER_SET_NAME | COLLATION_NAME     |
    +-----------------+-------------+---------------------+-------------+----------------------+----------------+------------+--------------------+--------------------+
    |               1 | id          | bigint unsigned      | NO          | NULL                 | auto_increment | PRI        | NULL               | NULL               |
    |               2 | customer_id | bigint unsigned      | NO          | NULL                 |                | MUL        | NULL               | NULL               |
    |               3 | status      | varchar(20)          | NO          | pending              |                |            | utf8mb4            | utf8mb4_unicode_ci |
    |               4 | created_at  | datetime(6)          | NO          | CURRENT_TIMESTAMP(6) |                |            | NULL               | NULL               |
    +-----------------+-------------+---------------------+-------------+----------------------+----------------+------------+--------------------+--------------------+
    4 rows in set (0.00 sec)

11. Exit the mysql client session.

    mysql> exit
    Bye