MySQL Show View

Created with Sketch.

MySQL Show View

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:

SHOW FULL TABLES
WHERE table_type = 'VIEW';

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:

SHOW FULL TABLES
[{FROM | IN } database_name]
WHERE table_type = 'VIEW';

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:

SHOW FULL TABLES IN sys
WHERE table_type='VIEW';

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:

SHOW FULL TABLES
[{FROM | IN } database_name]
LIKE pattern;

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:

SHOW FULL TABLES
FROM sys
LIKE '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.

SELECT *
FROM information_schema.tables;

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:

SELECT
table_name view_name
FROM
information_schema.tables
WHERE
table_type = 'VIEW' AND
table_schema = 'classicmodels';

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:

SELECT
table_name view_name
FROM
information_schema.tables
WHERE
table_type = 'VIEW' AND
table_schema = 'classicmodels' AND
table_name LIKE '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.

Leave a Reply

Your email address will not be published. Required fields are marked *