Summary: in this tutorial, you will learn how to show all views in a MySQL database by using the SHOW FULL TABLE statement or by querying information from the data dictionary.

MySQL Show View – using SHOW FULL TABLES statement

MySQL treats the views as tables with the type 'VIEW'. Therefore, to show all views in the current database, you use the SHOW FULL TABLES statement as follows:

Code language: SQL (Structured Query Language) (sql)

Because the SHOW FULL TABLES statement returns both tables and views, you need to add a WHERE clause to get the views only.

If you want to show all views from another database, you can use the FROM or IN clause as follows:

Code language: SQL (Structured Query Language) (sql)

In this syntax, you specify a database name from which you want to get the views after the FROM or IN clause.

For example, the following statement shows all views from the sys database:

Code language: SQL (Structured Query Language) (sql)

If you want to get views that match a pattern, you can use the LIKE clause as follows:

Code language: SQL (Structured Query Language) (sql)

The following statement uses the LIKE clause to find all views from the sys database, whose names start with the waits:

Code language: SQL (Structured Query Language) (sql)

Here is the output:

Note that the SHOW TABLES statement returns only the views that you have the privilege to access.

MySQL Show View –  using INFORMATION_SCHEMA database

The INFORMATION_SCHEMA database provides access to MySQL database metadata such as databases, tables, data types of columns, or privileges.

The INFORMATION_SCHEMA is sometimes referred to as a database dictionary or system catalog.

To show the views of a database, you use the tables table from the INFORMATION_SCHEMA.

Code language: SQL (Structured Query Language) (sql)

Here’s the partial output:

The columns which are relevant to the views are:

• The table_schema column stores the schema or database of the view (or table).
• The table_name column stores the name of the view (or table).
• The table_type column stores the type of tables: BASE TABLE for a table, VIEW for a view, or SYSTEM VIEW for an INFORMATION_SCHEMA table.

For example, this query returns all views from the classicmodels database:

Code language: SQL (Structured Query Language) (sql)

To find the views that match a pattern, you use the table_name column. For example, this query finds all views whose names start with customer:

Code language: SQL (Structured Query Language) (sql)

Summary

• Use the SHOW FULL TABLE with the type_type VIEW to return all views from the current database.
• Use the SHOW FULL TABLE FROM (or IN) statement to get all views in a specified database.
• Add the LIKE clause to the SHOW FULL TABLE statement to get the views that match a pattern.
• Query data from the table information_schema.tables to get the views in a database.