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”.

Steps to view table schema:

Connect to the server using the mysql client.

$ mysql -h db.example.net -u app -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 38
Server version: 8.0.44 MySQL Community Server - GPL

Copyright (c) 2000, 2025, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

Select the database that contains the table.
```
mysql> USE appdb;
Database changed
```

Confirm the current database context.

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

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`.

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

mysql> SHOW TABLE STATUS LIKE 'orders'\G
*************************** 1. row ***************************
           Name: orders
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 9
 Avg_row_length: 1820
    Data_length: 16384
Max_data_length: 0
   Index_length: 16384
      Data_free: 0
 Auto_increment: 10
    Create_time: 2025-12-24 13:52:40
    Update_time: 2025-12-24 13:58:58
     Check_time: NULL
      Collation: utf8mb4_0900_ai_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

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          | int           | NULL               | NO   | PRI | NULL              | auto_increment    | select,insert,update |         |
| customer_id | int           | NULL               | NO   | MUL | NULL              |                   | select,insert,update |         |
| status      | varchar(20)   | utf8mb4_0900_ai_ci | NO   |     | NULL              |                   | select,insert,update |         |
| total       | decimal(10,2) | NULL               | NO   |     | NULL              |                   | select,insert,update |         |
| created_at  | datetime      | NULL               | YES  |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED | select,insert,update |         |
+-------------+---------------+--------------------+------+-----+-------------------+-------------------+----------------------+---------+
5 rows in set (0.00 sec)

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

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` int NOT NULL AUTO_INCREMENT,
  `customer_id` int NOT NULL,
  `status` varchar(20) NOT NULL,
  `total` decimal(10,2) NOT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  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 AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

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

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 | Visible | Expression |
+--------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| orders |          0 | PRIMARY                |            1 | id          | A         |           9 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| orders |          1 | idx_orders_customer_id |            1 | customer_id | A         |           3 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+--------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.00 sec)

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

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.

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          | int           | NO          | NULL              | auto_increment    | PRI        | NULL               | NULL               |
|                2 | customer_id | int           | NO          | NULL              |                   | MUL        | NULL               | NULL               |
|                3 | status      | varchar(20)   | NO          | NULL              |                   |            | utf8mb4            | utf8mb4_0900_ai_ci |
|                4 | total       | decimal(10,2) | NO          | NULL              |                   |            | NULL               | NULL               |
|                5 | created_at  | datetime      | YES         | CURRENT_TIMESTAMP | DEFAULT_GENERATED |            | NULL               | NULL               |
+------------------+-------------+---------------+-------------+-------------------+-------------------+------------+--------------------+--------------------+
5 rows in set (0.00 sec)

Exit the mysql client session.
```
mysql> exit
Bye
```

Author: Mohd Shakir Zakaria
Mohd Shakir Zakaria is a cloud architect with deep roots in software development and open-source advocacy. Certified in AWS, Red Hat, VMware, ITIL, and Linux, he specializes in designing and managing robust cloud and on-premises infrastructures.