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 recommend thoroughy testing on a production-like test system first before moving to production.
tags cloud
popular searches
free download for mysql database server 5.1.5, php, gearman, bison, linux, java, ubuntu, mysql, source code, mysql mysql, tools, install mysql, mysql initialization, install cairo, laptop
Similar Tutorials:
- MySQL table_cache, information_schema, and open files limit
- MySQL unique key vs primary key constraints
- Initializing MySQL database after installation
- all-schemas: Tool to run multiple SQL queries on a MySQL database server
- How to see table definition statements in MySQL?
Tutorials in 'Database Management Systems > MySQL' (more):
- How to delete rows in table when PK is referenced in another table
- Unique primary keys(PKs) for MySQL using Redis
- How to rename MySQL DB by moving tables
- How to set innodb_flush_log_at_timeout in MySQL 5.6?
- Is MySQL 5.6 slower than MySQL 5.5? No, it's faster
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 Hasan on Jun 08, 2014