MySQL query to find all views in a database

Category:

Tutorials > Database Management Systems > MySQL > Tips and Tricks

You might find it useful to list all the views/tables in a particular database. I am going to show you three different methods to get the lists using GUI and command line tools. Replace database_name with the actual database name.

Method1:

To get the list of views in a particular database using MySQL command line interface (mysql), you'll run the SQL below

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

Similarly, you can run the following SQL to get the list of all tables.

SHOW FULL TABLES IN database_name WHERE TABLE_TYPE LIKE 'BASE TABLE';

Method2:

You can also list all the views using the SQL below.

SELECT TABLE_NAME FROM information_schema.`TABLES` WHERE TABLE_TYPE LIKE 'VIEW' AND TABLE_SCHEMA LIKE 'database_name';

Similarly, you can run the following SQL to get the list of all tables.

SELECT TABLE_NAME FROM information_schema.`TABLES` WHERE TABLE_TYPE LIKE 'BASE TABLE' AND TABLE_SCHEMA LIKE 'database_name';

Method3:

You can also use MySQL administrator tool to find the list of all views/tables in a particular database

Steps:

Connect to the database server.
On the left hand side pane (top section), click on Catalogs option.
The list of schemas in the database server will show up in the bottom section on the left.
Click on the database name that you want to select.
The right hand pane should change with the list of all tables in the selected database.
Click on Views tab at the top to list all the views in the database.

Did this tutorial help a little? How about buy me a cup of coffee?

Please feel free to use the comments form below if you have any questions or need more explanation on anything. I do not guarantee a response.

IMPORTANT: You must thoroughy test any instructions on a production-like test environment first before trying anything on production systems. And, make sure it is tested for security, privacy, and safety. See our terms here.