Record (Present) Tables in a MySQL Database

When managing MySQL database servers, one of the crucial frequent duties you’ll carry out is to get accustomed to the surroundings. This consists of itemizing databases that reside on the server, displaying the database tables, or fetching details about person accounts and their privileges .

This text exhibits how you can checklist tables in a MySQL or MariaDB database through the command line.

Present MySQL Tables #

To get an inventory of the tables in a MySQL database, use the mysql shopper software to connect with the MySQL server and run the SHOW TABLES command.

Entry the MySQL server:

mysql -u person -p

From inside the MySQL shell, swap to the database utilizing the USE assertion:

“>USE database_name;

Execute the next command to get an inventory of all tables and views within the present database:

“>SHOW TABLES;

The output will look one thing like this:

+—————————-+
| Tables_in_database_name |
+—————————-+
| actions |
| permissions |
| permissions_roles |
| permissions_users |
| roles |
| roles_users |
| settings |
| customers |
+—————————-+
eight rows in set (0.00 sec)

The non-compulsory FULL modifier will present the desk kind as a second output column.

“>SHOW FULL TABLES;

The output will look one thing like this:

+—————————-+————+
| Tables_in_database_name | Table_type |
+—————————-+————+
| actions | VIEW |
| permissions | BASE TABLE |
| permissions_roles | BASE TABLE |
| permissions_users | BASE TABLE |
| roles | BASE TABLE |
| roles_users | BASE TABLE |
| settings | BASE TABLE |
| customers | BASE TABLE |
+—————————-+————+

eight rows in set (0.00 sec)

To get an inventory of the tables with out switching to the database, use both the FROM or IN clause adopted by the database title:

“>SHOW TABLES FROM database_name;

The LIKE clause can be utilized to filter the output of the SHOW TABLES command in keeping with a selected sample.

“>SHOW TABLES LIKE sample;

For instance, the next assertion will return all databases which names begins with ‘open’:

“>SHOW TABLES LIKE ‘permissions%’;+——————————————-+
| Tables_in_database_name (permissions%) |
+——————————————-+
| permissions |
| permissions_roles |
| permissions_users |
+——————————————-+
three rows in set (0.00 sec)

The p.c signal (%) means zero, one, or a number of characters.

Present MySQL Tables from the Command Line #

To get details about the tables from the Linux shell, you need to use both the mysql -e command or the mysqlshow command that shows databases and tables info.

That is particularly usefully while you need to work together with your MySQL databases utilizing shell scripts.

Run the next command in your terminal to indicate an inventory of all databases:

mysql -u person -p -e ‘SHOW TABLES FROM database_name;’

The output will show an inventory of all tables:

+—————————-+
| Tables_in_database_name |
+—————————-+
| actions |
| permissions |
| permissions_roles |
| permissions_users |
| roles |
| roles_users |
| settings |
| customers |
+—————————-+

Right here is an instance utilizing the mysqlshow command:

mysqlshow database_name

You’ll be able to filter the output with the grep command.

Conclusion #

To get details about the tables in a MySQL database, use the SHOW TABLES command.

Be at liberty to go away a remark in case you have any questions.

Supply

Germany Devoted Server

Leave a Reply