MySQL query to find all views in a database

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 recommend thoroughy testing on a production-like test system first before moving to production.