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?
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.
tags cloud
popular searches
free download for mysql database server 5.1.5, bison, gearman, source code, php, install cairo, java, mysql, laptop, install mysql, linux, mysql mysql, mysql initialization, tools, ubuntu
Similar Tutorials:
- MySQL Performance Optimization Part I
- Download all maatkit scripts through a single command
- Seeing indexes in a MySQL table
- Transfering MySQL Administrator/Query Browser connection details
- How to speed up a Mysql replica?
Tutorials in 'Database Management Systems > MySQL' (more):
- What should be monitored on a busy Database/MysQL server?
- How to speed up a Mysql replica?
- The server requested authentication method unknown to the client [phpmyadmin]
- How to set default values for MySQL JSON columns
- How to delete rows in table when PK is referenced in another table
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