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 to get a list of MySQL views?
To get a list of MySQL views, we can use the SELECT command with LIKE operator. Let us see the syntax first.
mysql> SELECT TABLE_SCHEMA, TABLE_NAME -> FROM information_schema.tables -> WHERE TABLE_TYPE LIKE 'VIEW';
The following is the output that displays the total number of views.
+--------------+-----------------------------------------------+ | TABLE_SCHEMA | TABLE_NAME | +--------------+-----------------------------------------------+ | sys | version | | sys | innodb_buffer_stats_by_schema | | sys | x$innodb_buffer_stats_by_schema | | sys | innodb_buffer_stats_by_table | | sys | x$innodb_buffer_stats_by_table | | sys | schema_object_overview | | sys | schema_auto_increment_columns | | sys | x$schema_flattened_keys | | sys | schema_redundant_indexes | | sys | ps_check_lost_instrumentation | | sys | latest_file_io | | sys | x$latest_file_io | | sys | io_by_thread_by_latency | | sys | x$io_by_thread_by_latency | | sys | io_global_by_file_by_bytes | | sys | x$io_global_by_file_by_bytes | | sys | io_global_by_file_by_latency | | sys | x$io_global_by_file_by_latency | | sys | io_global_by_wait_by_bytes | | sys | x$io_global_by_wait_by_bytes | | sys | io_global_by_wait_by_latency | | sys | x$io_global_by_wait_by_latency | | sys | innodb_lock_waits | | sys | x$innodb_lock_waits | | sys | memory_by_user_by_current_bytes | | sys | x$memory_by_user_by_current_bytes | | sys | memory_by_host_by_current_bytes | | sys | x$memory_by_host_by_current_bytes | | sys | memory_by_thread_by_current_bytes | | sys | x$memory_by_thread_by_current_bytes | | sys | memory_global_by_current_bytes | | sys | x$memory_global_by_current_bytes | | sys | memory_global_total | | sys | x$memory_global_total | | sys | schema_index_statistics | | sys | x$schema_index_statistics | | sys | x$ps_schema_table_statistics_io | | sys | schema_table_statistics | | sys | x$schema_table_statistics | | sys | schema_table_statistics_with_buffer | | sys | x$schema_table_statistics_with_buffer | | sys | schema_tables_with_full_table_scans | | sys | x$schema_tables_with_full_table_scans | | sys | schema_unused_indexes | | sys | schema_table_lock_waits | | sys | x$schema_table_lock_waits | | sys | statement_analysis | | sys | x$statement_analysis | | sys | statements_with_errors_or_warnings | | sys | x$statements_with_errors_or_warnings | | sys | statements_with_full_table_scans | | sys | x$statements_with_full_table_scans | | sys | x$ps_digest_avg_latency_distribution | | sys | x$ps_digest_95th_percentile_by_avg_us | | sys | statements_with_runtimes_in_95th_percentile | | sys | x$statements_with_runtimes_in_95th_percentile | | sys | statements_with_sorting | | sys | x$statements_with_sorting | | sys | statements_with_temp_tables | | sys | x$statements_with_temp_tables | | sys | user_summary_by_file_io_type | | sys | x$user_summary_by_file_io_type | | sys | user_summary_by_file_io | | sys | x$user_summary_by_file_io | | sys | user_summary_by_statement_type | | sys | x$user_summary_by_statement_type | | sys | user_summary_by_statement_latency | | sys | x$user_summary_by_statement_latency | | sys | user_summary_by_stages | | sys | x$user_summary_by_stages | | sys | user_summary | | sys | x$user_summary | | sys | host_summary_by_file_io_type | | sys | x$host_summary_by_file_io_type | | sys | host_summary_by_file_io | | sys | x$host_summary_by_file_io | | sys | host_summary_by_statement_type | | sys | x$host_summary_by_statement_type | | sys | host_summary_by_statement_latency | | sys | x$host_summary_by_statement_latency | | sys | host_summary_by_stages | | sys | x$host_summary_by_stages | | sys | host_summary | | sys | x$host_summary | | sys | wait_classes_global_by_avg_latency | | sys | x$wait_classes_global_by_avg_latency | | sys | wait_classes_global_by_latency | | sys | x$wait_classes_global_by_latency | | sys | waits_by_user_by_latency | | sys | x$waits_by_user_by_latency | | sys | waits_by_host_by_latency | | sys | x$waits_by_host_by_latency | | sys | waits_global_by_latency | | sys | x$waits_global_by_latency | | sys | metrics | | sys | processlist | | sys | x$processlist | | sys | session | | sys | x$session | | sys | session_ssl_status | +--------------+-----------------------------------------------+ 100 rows in set (0.01 sec)
Updated on: 30-Jul-2019
3K+ Views
Advertisements