How to Show Views in MySQL

Managing and viewing database objects, especially views, is crucial for effective database management. Views provide data abstraction, making complex queries more straightforward and reusable, while also allowing controlled access to sensitive data. Regularly checking views is a routine part of daily database management, and there is a high demand for simple methods to list all views in a database.

In this article, we will talk about various methods to check MySQL views. In particular, we will explore the default commands for listing views in MySQL databases and describe how to use them from the command line or via the MySQL GUI tools.

What are views in MySQL

MySQL views are stored queries that function as virtual tables. Although they do not contain any data, they enable the retrieval and editing of data from other base tables. Users interact with views as if they were regular tables, complete with columns, rows, and data. However, views only display specific information required, without exposing any database schema details or other sensitive data.

The main benefits of MySQL views include:

• Abstraction: Views let the database users access and manage data without interacting with complex database schema structures.
• Security: By using views, users can access necessary information without direct access to the underlying tables. It enhances control over data and permissions.
• Consistency: Views provide a consistent presentation of data to users. Additionally, view definitions can be updated to reflect changes in the underlying tables without modifying the applications that utilize the views.

Let us review the available methods to list views in a MySQL database.

Methods to show views in MySQL

Getting the list of all views in a specific MySQL database can be crucial in several cases. Firstly, this data is necessary for database documentation. Secondly, identifying and reviewing all database views can help us optimize performance, debug data retrieval procedures, audit the database, and more.

To obtain a list of views in a particular MySQL database, we have two methods:

• Dedicated SQL queries
• GUI-based tools

In this guide, we will explore both methods. We will use a command-line tool for the SQL query method and dbForge Studio for MySQL for the GUI method.

Using the SHOW FULL TABLES statement

MySQL treats views as tables of the specific VIEW type. Therefore, we can use the standard SHOW FULL TABLES statement with the WHERE clause to retrieve the list of all tables belonging to the VIEW type. The query syntax is:

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

We are using the sakila test database to illustrate the cases. Therefore, let us retrieve the list of views from that particular database. Open the Command Prompt, connect to your MySQL server, and execute the following query:

SHOW FULL TABLES
FROM sakila
WHERE table_type = 'VIEW';

Also, we can filter the list of views with the help of the LIKE clause:

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

In this query, pattern stands for the filtering condition.

Assume we want to view the list of views containing the keyword sales in their names. The query is:

SHOW FULL TABLES
IN sakila 
LIKE 'sales%';

Using the SHOW TABLE STATUS statement

The SHOW TABLE STATUS statement can retrieve the list of the database tables and filter them by the Comment column. In this scenario, we require the list of tables where this column defines that the table is a view:

SHOW TABLE STATUS FROM sakila 
WHERE Comment = 'VIEW';

We use dbForge Studio for MySQL to illustrate this use case. This robust IDE for MySQL allows us to write SQL code and execute it directly against the desired database. Besides, the results are displayed in a user-friendly table format, making them easier to analyze and process.

SHOW TABLE STATUS provides detailed information about each table, including type, creation time, update time, and more. However, it may lead to slower performance, particularly in larger databases, and the output may include both tables and views, requiring further sorting and filtering.

Using INFORMATION_SCHEMA.TABLES

INFORMATION_SCHEMA.TABLES is a database that contains information about all databases managed by a specific MySQL instance. This includes information on databases, tables, their types, permissions, views, and more.

Querying INFORMATION_SCHEMA.TABLES allows us to retrieve the list of all views across all databases in the specified instance simultaneously. The query is:

SELECT *
FROM information_schema.tables 
WHERE table_type   = 'VIEW';

We utilize dbForge Studio for MySQL as its output is presented in a better-organized way.

The relevant columns for views are:

• table_schema is the name of the database/schema
• table_name is the name of the view (or table)

To retrieve the list of views belonging to any particular database, we need to specify the name of the database in the query:

SELECT
table_name view_name
FROM information_schema.TABLES
WHERE table_type = 'VIEW'
AND TABLE_SCHEMA = 'sakila';

To filter views by name, we can modify the query using the LIKE clause:

SELECT
table_name view_name
FROM information_schema.TABLES
WHERE table_type = 'VIEW'
AND TABLE_SCHEMA = 'sakila'
AND table_name LIKE '%list%';

Using dbForge Studio for MySQL

dbForge Studio for MySQL is a multi-featured GUI-based solution designed for all aspects of database development, management, and administration in MySQL and MariaDB. It significantly simplifies database-related tasks by reducing the need for manual coding. This way, it enhances overall performance and productivity.

The software includes an advanced coding assistance tool with a rich set of options, such as context-based code auto-completion, code validation, code snippets, code formatting, a comprehensive snippet library, and more. All coding jobs can be accelerated, and the quality of the output is improved.

Earlier in this article, we demonstrated the work of dbForge Studio for MySQL by writing codes and executing them against the necessary database (sakila) directly from the Code Editor. All scripts executed via the Command Prompt are also easily prepared and executed via the Studio.

Also, dbForge Studio for MySQL offers a Database Explorer which allows users to visually inspect databases and their structures.

To view the list of views in any database, follow these steps:

• Select the desired database from the list and expand the database node
• Navigate to the Views node and expand it to display all views in the selected database
• Expand any view node to see its specific details

You can list views in all databases using the same method.

Conclusion

dbForge Studio for MySQL is one of the most powerful and feature-rich MySQL GUI tools available. It offers an extensive array of features and a comprehensive suite of visualization and automation options, enabling users to handle all database-related tasks with a single solution.

Writing high-quality SQL code, managing databases, administering MySQL instances and users, and many other tasks can be done efficiently with dbForge Studio for MySQL. Experience the full capabilities of the Studio with a FREE 30-day trial!