How to get a list of MySQL views?

MySQLMySQLi Database

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)
raja
Published on 21-Nov-2018 12:50:41
Advertisements