MySQL Get List of Views From the Database

In this tutorial, we will see different methods to show all views in the MySQL database. It is very easy to get the list of all views that have been created already. We will demonstrate two methods in this tutorial to display the available views in the database. So, let’s get started!

Before We Start

Before we proceed to demonstrate the examples to display the list of views, we will create two views in our database first.

USE journaldev;

CREATE VIEW getData AS
SELECT * FROM emps;

CREATE VIEW getDataReverse AS
SELECT * FROM emps ORDER BY id DESC;Code language: SQL (Structured Query Language) (sql)

Now let’s see how can we get the list of views in our database.

Show Views using SHOW FULL TABLES Statement

In MySQL, all views are treated as a table with the type ‘view’. Therefore, we can use the SHOW FULL TABLES statement to display all the tables and views from our database.

If we use the condition or a LIKE operator, we can find the views.

To get the list of views from the currently selected database, you can use the following syntax-

SHOW FULL TABLES 
WHERE table_type = 'VIEW';
Code language: SQL (Structured Query Language) (sql)

Here, you must select the database using the “USE db_name” statement to get the list of views from the database.

Note that, the table_type is a column name when you fetch the table list.

To get the list of views from the other databases, you can use the following syntax-

SHOW FULL TABLES 
[ { FROM | IN } db_name ]
WHERE table_type = 'VIEW';
Code language: SQL (Structured Query Language) (sql)

Here, you can use either the ‘FROM’ or ‘IN’ keyword along with the database name that you want to get the list of views from.

Let’s write a statement to get the list of views from the currently selected database.

SHOW FULL TABLES
WHERE table_type='VIEW';
Code language: SQL (Structured Query Language) (sql)

As you can see, we have received the list of views available in our database.

Now, let’s display the views from another database ‘sys’ which is already defined in the MySQL DBMS.

SHOW FULL TABLES IN sys 
WHERE table_type='VIEW';
Code language: SQL (Structured Query Language) (sql)

Here is the screenshot of the partial output of the views.

We can also use the LIKE operator to get the list of views.

SHOW FULL TABLES 
FROM sys
LIKE '%host%';
Code language: SQL (Structured Query Language) (sql)

Note that, you will get the base table names as well if you use the LIKE operator as it doesn’t check the table_type.

Show Views using INFORMATION SCHEMA Statement

The INFORMATION SCHEMA database is a database dictionary or a system catalog which consists of MySQL database metadata such as databases, tables, datatypes of columns and privileges.

To get the information about tables and views, you can use the ‘tables’ table from the information_schema database.

select * from information_schema.tables;Code language: SQL (Structured Query Language) (sql)

Here is the partial output received from the above query.

Here, we will need three columns to find the views from the desired database.

• table_schema – It is the schema or a database name of the table/view.
• table_name – As stated earlier, views are considered as a table in MySQL. So, views are also listed under the tables. This column consists of views or base table names.
• table_type – It specifies the type of the table, i.e., BASE TABLE or VIEW.

Let’s display the list of views using the information_schema database and the above terminologies.

select table_name as view_name from information_schema.tables 
where table_type='VIEW' and table_schema='journaldev';Code language: SQL (Structured Query Language) (sql)

Here, we are displaying the table names that are stored in the information_schem.tables table. We have specified a condition using the WHERE clause to check if the type of a table is ‘view’ and if those tables belong to the database ‘journaldev’.

The above query will generate the below result.

As you can see, the journaldev database consists of two views which are listed here.

Summary

In this tutorial, we have learned-

• Listing all views using the SHOW FULL TABLES statement.
• What is the information_schema database and its use.
• Listing all views using information_schema.tables table.