How to list tables in MariaDB

Galaxy Glossary

How do I list all tables in MariaDB?

SHOW TABLES returns the names of all base tables and views in the current or specified MariaDB database.

Sign up for the latest in SQL knowledge from the Galaxy Team!

Description

How do I list all tables in MariaDB?

Execute SHOW TABLES; in any SQL editor connected to MariaDB. The server returns one row per table or view in the current database.

How can I list tables from another database?

Add FROM db_name to the statement, e.g., SHOW TABLES FROM shop;. This is useful when you are connected to a different database but need a quick inventory of shop.

How do I filter table names?

Use LIKE 'pattern' for simple wildcards or WHERE for boolean expressions. Example: SHOW TABLES LIKE 'cust%'; lists only tables whose names start with cust.

What columns does FULL add?

SHOW FULL TABLES adds a Table_type column that tells whether each entry is a BASE TABLE or a VIEW. This helps when your schema mixes the two.

Can I see tables I can write to?

Combine SHOW TABLES with the information_schema.user_privileges view, or rely on GUI tools. SHOW TABLES itself only lists objects; it doesn’t check permissions.

Why use SHOW TABLES over querying information_schema?

SHOW TABLES is shorter, cached, and optimized for quick listing. information_schema.tables offers richer metadata but is slower and requires more typing.

Best practices for production databases?

Always qualify with FROM db_name inside scripts to avoid surprises when the connection’s default database changes. Include FULL when distinguishing views from tables matters.

Why How to list tables in MariaDB is important

How to list tables in MariaDB Example Usage


-- List all base tables in the ecommerce database that begin with "O"
SHOW FULL TABLES FROM ecommerce WHERE Table_type = 'BASE TABLE' AND Tables_in_ecommerce LIKE 'O%';

How to list tables in MariaDB Syntax


SHOW [FULL] TABLES [FROM db_name] [LIKE 'pattern' | WHERE expr];

-- Example in ecommerce schema
-- 1. List everything in current db
a) SHOW TABLES;
-- 2. List only ecommerce tables in another db
b) SHOW FULL TABLES FROM ecommerce;
-- 3. List tables starting with "Orders"
c) SHOW TABLES LIKE 'Orders%';
-- 4. List only base tables (exclude views) with WHERE
d) SHOW FULL TABLES WHERE Table_type = 'BASE TABLE';

Common Mistakes

Omitting FROM db_name in scripts leads to wrong results when the connection’s default database changes. Fix by always qualifying the database: SHOW TABLES FROM ecommerce;
Assuming SHOW TABLES hides views. It lists both tables and views. Use SHOW FULL TABLES WHERE Table_type = 'BASE TABLE' to exclude views.

Frequently Asked Questions (FAQs)

Does SHOW TABLES require special privileges?

You need at least the SELECT privilege on the database. Without it, the command returns an empty set.

How do I see table row counts?

SHOW TABLES doesn’t return counts. Query information_schema.tables or run SELECT COUNT(*) on each table.

Can I list temporary tables?

SHOW TABLES lists only permanent tables. Temporary tables are visible only in the current session via information_schema.tables.

Want to learn about other SQL terms?

dbt tests

dbt tests are declarative assertions in dbt that automatically validate data quality during model builds.

dbt utils

dbt utils is a community-maintained dbt package that adds time-saving utility macros for analytics engineers, such as generating surrogate keys, safely unioning models, and automating date spines.

dbt Tutorial Glossary: Build, Test & Deploy SQL Models

dbt is an open-source framework that lets data teams transform raw warehouse tables into tested, documented, production-ready datasets using version-controlled SQL.

Trusted by top engineers on high-velocity teams

Assort Health

Curri

Rubie