Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How can we see the list of views stored in a particular MySQL database?
With the help of following queries, we can see the list of views stored in a particular database. We are using the database named ‘query’ here.
mysql> SELECT TABLE_NAME FROM information_schema.`TABLES` WHERE TABLE_TYPE LIKE'view' AND TABLE_SCHEMA LIKE 'query'; +-----------------------------+ | TABLE_NAME | +-----------------------------+ | customer_view | | first_view | | info | | info_less | | view_detail | | view_student_detail | | view_student_detail_columns | +-----------------------------+ 7 rows in set (0.01 sec) mysql> SHOW FULL TABLES IN query WHERE TABLE_TYPE LIKE 'VIEW'; +-----------------------------+------------+ | Tables_in_query | Table_type | +-----------------------------+------------+ | customer_view | VIEW | | first_view | VIEW | | info | VIEW | | info_less | VIEW | | view_detail | VIEW | | view_student_detail | VIEW | | view_student_detail_columns | VIEW | +-----------------------------+------------+ 7 rows in set (0.01 sec) mysql> SELECT TABLE_SCHEMA, TABLE_NAME -> FROM information_schema.tables -> WHERE TABLE_TYPE LIKE 'VIEW'AND TABLE_SCHEMA = 'query'; +--------------+-----------------------------+ | TABLE_SCHEMA | TABLE_NAME | +--------------+-----------------------------+ | query | customer_view | | query | first_view | | query | info | | query | info_less | | query | view_detail | | query | view_student_detail | | query | view_student_detail_columns | +--------------+-----------------------------+ 7 rows in set (0.05 sec)
Updated on: 17-Feb-2020
89 Views
Advertisements