How to get a list of MySQL views?

• Get a list of MySQL views from a specific database

  Get views from specific database using information_schema virtual database

  select table_name as view_name from information_schema.views where table_schema = "learn_sql";
  

  Output

  +-------------+
  | view_name   |
  +-------------+
  | user_orders |
  +-------------+
  1 row in set (0.00 sec)
  

  Get the views from learn_sql database using filter

  show full tables in learn_sql where table_type='VIEW';
  

  Output

  +---------------------+------------+
  | Tables_in_learn_sql | Table_type |
  +---------------------+------------+
  | user_orders         | VIEW       |
  +---------------------+------------+
  1 row in set (0.00 sec)
  

  The above SQL query retrieves a list of all views in the database "learn_sql" by filtering results where the table_type column is equal to 'VIEW'.

• Get all views from database without specifying database name

  show full tables where table_type = 'VIEW';
  

  Output

  +---------------------+------------+
  | Tables_in_learn_sql | Table_type |
  +---------------------+------------+
  | user_orders         | VIEW       |
  +---------------------+------------+
  1 row in set (0.00 sec)
  

• Get all views along with sys database views

  select table_schema, table_name from information_schema.tables where table_type like 'VIEW';
  

  Output

  +--------------+-----------------------------------------------+
  | TABLE_SCHEMA | TABLE_NAME                                    |
  +--------------+-----------------------------------------------+
  | learn_sql    | user_orders                                   |
  | my_form      | employeenames                                 |
  | sys          | host_summary                                  |
  | sys          | host_summary_by_file_io                       |
  | sys          | host_summary_by_file_io_type                  |
  | sys          | host_summary_by_stages                        |
  | sys          | host_summary_by_statement_latency             |
  | sys          | host_summary_by_statement_type                |
  | sys          | innodb_buffer_stats_by_schema                 |
  | sys          | innodb_buffer_stats_by_table                  |
  | sys          | innodb_lock_waits                             |
  | sys          | io_by_thread_by_latency                       |
  | sys          | io_global_by_file_by_bytes                    |
  | sys          | io_global_by_file_by_latency                  |
  | sys          | io_global_by_wait_by_bytes                    |
  | sys          | io_global_by_wait_by_latency                  |
  | sys          | latest_file_io                                |
  

Back to code snippet queries related sql