How to View Schema in MariaDB

Galaxy Glossary

How do I view the schema of a table in MariaDB?

Displays database, table, and column structure details using SHOW and INFORMATION_SCHEMA queries.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

How do I list every table in my MariaDB database?

Run SHOW TABLES; in your current database to return all table names instantly.Add FROM ecommerce; when connected to another schema.

Can I filter tables by name pattern?

Use SHOW TABLES LIKE 'ord%'; to retrieve only tables whose names start with ord, such as Orders and OrderItems.

How do I view a table’s column definitions fast?

Execute DESCRIBE Orders; or its synonym SHOW COLUMNS FROM Orders; to list each column, data type, nullability, default, and key info.

Example: inspect Orders table

DESCRIBE Orders; returns columns like id INT PK, customer_id INT FK, and total_amount DECIMAL(10,2).

What if I need the exact DDL for a table?

Run SHOW CREATE TABLE Products\G to receive the full CREATE TABLE statement, including indexes and constraints—ideal for migrations and backups.

How can I query INFORMATION_SCHEMA for deeper inspection?

Query INFORMATION_SCHEMA.COLUMNS to aggregate metadata or search across tables.Example: list all DECIMAL columns in the ecommerce schema.

Example query

SELECT table_name, column_name FROM information_schema.columns WHERE table_schema='ecommerce' AND data_type='decimal';

Best practices for viewing schema data

Work in a read-only client, qualify schema names in multi-db servers, and export results to version control for future reference.

Common mistakes and fixes

FORGETTING FROM db_name—returns tables from the wrong database. Always specify the schema when not connected directly.USING DESCRIBE *—wildcards not allowed; DESCRIBE each table individually or loop in a script.

Need more detail?

Combine SHOW CREATE TABLE with a quick mysqldump --no-data to capture the entire database schema without row data.

Why How to View Schema in MariaDB is important

How to View Schema in MariaDB Example Usage


-- List schema for the Orders table in the ecommerce database
USE ecommerce;
DESCRIBE Orders;
-- Get full DDL for Products
aSHOW CREATE TABLE Products\G

How to View Schema in MariaDB Syntax


SHOW TABLES [FROM db_name] [LIKE 'pattern'];
SHOW COLUMNS FROM table_name [IN db_name] [LIKE 'pattern'];
DESCRIBE table_name [column_name];
SHOW CREATE TABLE table_name; 
-- INFORMATION_SCHEMA example
SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'ecommerce' AND TABLE_NAME = 'Orders';

Common Mistakes

Running SHOW TABLES without selecting or specifying a database. This lists tables from the default or last-used schema, leading to confusion. Fix by executing USE ecommerce; or adding FROM ecommerce.
Expecting DESCRIBE * to list all tables at once. DESCRIBE only handles one table. Use SHOW TABLES to loop through table names or query INFORMATION_SCHEMA for bulk metadata.