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?

Buy me a coffee at ko-fi.com

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.

Comments (write a comment):

Great blog. Posted by: jaffa on Feb 02, 2012

How do get the base table of a View in Mysql ? Posted by: Murad on Oct 15, 2012

This information is very helpful for us. Posted by: Razibul H on Jun 08, 2014

leave a comment on tutorial leave a comment