In this tutorial, you have learned how to use the MySQL SHOW TABLES statement to list all tables in a particular database. It’s important to note that if you don’t have privileges for a base table or view, it won’t show up in the result set of the SHOW TABLES command. The following statement is equivalent to the statement above but it uses IN instead of FROM. The following example demonstrates how to show tables that start with 'time' > SHOW TABLES FROM mysql LIKE 'time%' 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.
#MYSQL GET LIST OF USERS FULL#
> SHOW FULL TABLES WHERE table_type = 'VIEW' 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)Ī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 shows all tables in the classicmodels database that start with the letter p, you use the following statement: > SHOW TABLES LIKE 'p%' Now, you issue the SHOW FULL TABLES command: > SHOW FULL TABLES UNION SELECT contactFirstName, contactLastName, phone CREATE VIEW contactsĪS SELECT lastName, firstName, extension as phone Log in as the MySQL Root User Start by logging in to the VPS via SSH as the root user. Follow the steps below for more information. 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. Even though there is no such command, users can use a MySQL query and get a full list of users in a given MySQL database server. 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 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 Switch to a specific database using the USE statement.Login to the MySQL database server using a MySQL client such as mysql.To list tables in a MySQL database, you follow these steps:
0 kommentar(er)
