How to List All MySQL Views in a Specific Database?

Retrieving a Comprehensive List of MySQL Views

Problem Description:

Enlisting all views stored within a particular database has proven problematic. Employing the convention SELECT table_name FROM information_schema.views WHERE information_schema.views.table_schema LIKE 'view%' produces an empty set, despite the presence of views.

Other attempts using SELECT * from views and SELECT * from tables yield the error message "Incorrect database name."

Root Cause:

The aforementioned commands are unable to access the information schema due to MySQL's implicit change to the mysql.bak database after switching to the information_schema database using the use command.

Solution:

To successfully retrieve a list of all views in a database, execute the following query:

SHOW FULL TABLES IN database_name WHERE TABLE_TYPE LIKE 'VIEW';

Replace database_name with the name of the database containing the views you wish to enumerate. This modified approach employs the SHOW FULL TABLES command instead of SELECT * to obtain a detailed list of database tables and their types. By specifying the TABLE_TYPE filter as 'VIEW', the query yields only tables that are specifically designated as views.

The above is the detailed content of How to List All MySQL Views in a Specific Database?. For more information, please follow other related articles on the PHP Chinese website!