How to get a list of MySQL views?

In MySQL, to retrieve the list of all views in a database, you can use the VIEWS table within the INFORMATION_SCHEMA database. INFORMATION_SCHEMA is an internal database that provides information about all other databases, including views, tables, columns, and more.

Method One: Using INFORMATION_SCHEMA.VIEWS

You can execute the following SQL query to list all views in a specific database:

sql
SELECT TABLE_NAME AS ViewName
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_SCHEMA = 'your_database_name';

Here, TABLE_NAME returns the name of the view, and TABLE_SCHEMA specifies the database name. Replace 'your_database_name' with the name of the target database.

Method Two: Using SHOW FULL TABLES Query

Another approach is to use the SHOW FULL TABLES statement to differentiate between tables and views:

sql
SHOW FULL TABLES IN your_database_name WHERE TABLE_TYPE LIKE 'VIEW';

Here, your_database_name is the name of the database you are querying. This command returns the list of all views in the database.

Example:

Suppose you have a database named school containing multiple views and tables. To retrieve all views:

Using INFORMATION_SCHEMA.VIEWS Query Example:

sql
SELECT TABLE_NAME AS ViewName
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_SCHEMA = 'school';

This query lists all views in the school database.

Using SHOW FULL TABLES Query Example:

sql
SHOW FULL TABLES IN school WHERE TABLE_TYPE LIKE 'VIEW';

This displays the names of all views in the school database.

With these two methods, you can easily retrieve the list of views in a MySQL database. These techniques are highly valuable for database management and maintenance tasks.