Listing Databases and Tables in MySQL and MariaDB

mysql mariadb list databases tables

This guide explains how to list databases and tables using MySQL or MariaDB using simple commands.

Managing your databases from the Linux shell is quick and efficient compared to some of the bloated database management tools available.

Here’s a quick primer on seeing what databases and tables you’ve got set up on your MySQL or MariaDB server.

Listing Databases

Once you’ve logged in to your database, simply enter the following to list your databases:

SHOW DATABASES;

You’ll get a list of all of the databases set up on the system. Easy!

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| mydatabase         |
+--------------------+

Note that:

  • Some databases: mysql, information_schema, performance_schema, are present in the default MySQL/MariaDB installation and are used by the database software itself – don’t mess with them!
  • Make sure you end all MYSQL commands with “;” (not including speech marks). Otherwise, they won’t be executed

Filtering the list of databases

If you want to only show databases matching a certain name, use the LIKE statement. This example shows only databases starting with my:

SHOW DATABASES LIKE 'my%';

+--------------------+
| Database           |
+--------------------+
| mydatabase         |
+--------------------+

Selecting a Database

Once you’ve identified the database you want to use from the list, use it:

USE mydatabase;

Listing Tables

Unsurprisingly, listing the tables in a database is just as simple. Once you’ve selected a database for use:

SHOW TABLES;

And you’ll receive a list of tables ready to query.

+-------------------------+
| Tables_in_classicmodels |
+-------------------------+
| users                   |
| posts                   |
| comments                |
+-------------------------+
SHARE:
nv-author-image

Brad Morton

I'm Brad, and I'm nearing 20 years of experience with Linux. I've worked in just about every IT role there is before taking the leap into software development. Currently, I'm building desktop and web-based solutions with NodeJS and PHP hosted on Linux infrastructure. Visit my blog or find me on Twitter to see what I'm up to.

Leave a Reply

Your email address will not be published. Required fields are marked *