How to Get Table Structure in MariaDB / MySQL
Sometime I need to fix a query error from PHP application. First thing I do is reproduce client's environment in my local dev PC. Modern PHP applications usually only require to clone the repository. Some legacy applications require to compress application files and download it locally.
As for MySQL / MariaDB databases, there are times when their access are rather limited to shell or terminal access. It's getting harder when the client only allow read-only access to database, as it means we cannot just use mysqldump command.
Luckily there are some workarounds for this issue. There are at least two ways to get a MySQL table’s structure using SQL queries, depends on which output you prefer.
- Column listing format
- SQL dump format
Note: I'm using Laravel's users table as an example in this article.
Column Listing Format
There are multiple options if you want column listing format:
Using DESCRIBE
To obtain information about table structure in column listing format we can use DESCRIBE:
MariaDB [laravel]> DESCRIBE users;
+-------------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+---------------------+------+-----+---------+----------------+
| id | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(255) | NO | | NULL | |
| email | varchar(255) | NO | UNI | NULL | |
| email_verified_at | timestamp | YES | | NULL | |
| password | varchar(255) | NO | | NULL | |
| remember_token | varchar(100) | YES | | NULL | |
| created_at | timestamp | YES | | NULL | |
| updated_at | timestamp | YES | | NULL | |
+-------------------+---------------------+------+-----+---------+----------------+
8 rows in set (0.000 sec)
Using SHOW COLUMNS
Similar output could be generated using SHOW COLUMNS query:
MariaDB [laravel_ws]> SHOW COLUMNS FROM users;
+-------------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+---------------------+------+-----+---------+----------------+
| id | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(255) | NO | | NULL | |
| email | varchar(255) | NO | UNI | NULL | |
| email_verified_at | timestamp | YES | | NULL | |
| password | varchar(255) | NO | | NULL | |
| remember_token | varchar(100) | YES | | NULL | |
| created_at | timestamp | YES | | NULL | |
| updated_at | timestamp | YES | | NULL | |
+-------------------+---------------------+------+-----+---------+----------------+
8 rows in set (0.000 sec)
SQL Dump Format
What if we want to replicate table structure? We can use SHOW CREATE TABLE to generate SQL format to create table structure. The output will be similar with output from mysqldump format.
MariaDB [laravel]> SHOW CREATE TABLE users;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| users | CREATE TABLE `users` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`email_verified_at` timestamp NULL DEFAULT NULL,
`password` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`remember_token` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `users_email_unique` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)
Final Words
I hope that you now know how to get table structure in MariaDB or MySQL. If you run into any issues or have any feedback feel free to drop a comment below.
