mysql show tables in database command line

In this case, you can use the FROM clause of the SHOW TABLES statement to specify the database from which you want to show the tables. Sometimes, you want to see the tables in the database that you are not connected to. > SHOW FULL TABLES WHERE table_type = 'VIEW' ġ row in set ( 0.00 sec) Code language: SQL (Structured Query Language) ( sql ) The following statement illustrates how to use the WHERE clause in the SHOW TABLES statement to list all the views in the classicmodels database. Or to show the tables that end with the string 'es', you use the following statement: > SHOW TABLES LIKE '%es' + -+-+ 9 rows in set ( 0.00 sec) Code language: SQL (Structured Query Language) ( sql )Īs you can see, all the tables are the base tables except for the contacts table which is a view.įor the database that has many tables, showing all tables at a time may not be intuitive.įortunately, the SHOW TABLES command provides you with an option that allows you to filter the returned tables using the LIKE operator or an expression in the WHERE clause as follows: SHOW TABLES LIKE pattern įor example, to show all tables in the classicmodels database that starts with the letter p, you use the following statement: > SHOW TABLES LIKE 'p%' ģ rows in set ( 0.00 sec) Code language: SQL (Structured Query Language) ( sql ) Now, you issue the SHOW FULL TABLES command: > SHOW FULL TABLES UNION SELECT contactFirstName, contactLastName, phoneįROM customers Code language: SQL (Structured Query Language) ( sql ) Let’s create a view in the classicmodels database called contacts that includes first name, last name, and phone from the employees and customers tables for the demonstration: CREATE VIEW contactsĪS SELECT lastName, firstName, extension as phone SHOW FULL TABLES Code language: SQL (Structured Query Language) ( sql ) To include the table type in the result, you use the following form of the SHOW TABLES statement. The SHOW TABLES command allows you to show if a table is a base table or a view. Show tables in the classicmodels database: > show tables Ĩ rows in set ( 0.00 sec) Code language: SQL (Structured Query Language) ( sql ) Mysql> Code language: SQL (Structured Query Language) ( sql ) Switch to classicmodels database: mysql> use classicmodels Connect to the MySQL database server: >mysql -u root -pĬode language: SQL (Structured Query Language) ( sql ) The following example shows you how to list the table in the classicmodels database. The following illustrates the syntax of the MySQL SHOW TABLES command: SHOW TABLES Ĭode language: SQL (Structured Query Language) ( sql ) MySQL SHOW TABLES examples Second, switch to a specific database using the USE statement.First, log in to the MySQL database server using a MySQL client such as mysql.To list tables in a MySQL database, you follow these steps:

Mysql show tables in database command line