{"id":7968,"date":"2019-08-28T21:34:19","date_gmt":"2019-08-29T04:34:19","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=7968"},"modified":"2024-01-03T19:14:00","modified_gmt":"2024-01-04T02:14:00","slug":"mysql-show-view","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-views\/mysql-show-view\/","title":{"rendered":"MySQL Show View"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to show all views in a MySQL database using the SHOW FULL TABLE<\/code> statement or by querying information from the data dictionary.<\/p>\n\n\n\n

The SHOW FULL TABLES<\/code> returns a list of views in a specified database. If you want to display the statement that creates the view, check out the SHOW CREATE VIEW<\/a> statement tutorial.<\/p>\n\n\n\n

MySQL Show View – Using SHOW FULL TABLES statement<\/h2>\n\n\n\n

MySQL treats the views as tables with the type 'VIEW'<\/code>. Therefore, you can use the SHOW FULL TABLES<\/a><\/code> statement to display all views<\/a> in the current database as follows:<\/p>\n\n\n

SHOW<\/span> FULL<\/span> TABLES<\/span> \nWHERE<\/span> table_type = 'VIEW'<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
If you want to show all views in a specific database,  you can use the FROM<\/code> or IN<\/code> clause in the SHOW FULL TABLES<\/code> statement:<\/p>\n\n\n
SHOW<\/span> FULL<\/span> TABLES<\/span>\n[{FROM<\/span> | IN<\/span> } database_name]\nWHERE<\/span> table_type = 'VIEW'<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax, you specify a database name from which you want to obtain the views after the FROM<\/code> or IN<\/code> clause.<\/p>\n\n\n\n
For example, the following statement shows all views from the sys<\/code> database:<\/p>\n\n\n
SHOW<\/span> FULL<\/span> TABLES<\/span> IN<\/span> sys<\/span> \nWHERE<\/span> table_type='VIEW'<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"\"<\/figure>\n\n\n\n
If you want to find views that match a pattern, you can use the LIKE<\/code> clause as follows:<\/p>\n\n\n
SHOW<\/span> FULL<\/span> TABLES<\/span>\n[{FROM<\/span> | IN<\/span> } database_name]\nLIKE<\/span> pattern;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
For example, the following statement uses the LIKE<\/code> clause to find all views from the sys<\/code> database, whose names start with the waits<\/code>:<\/p>\n\n\n
SHOW<\/span> FULL<\/span> TABLES<\/span> \nFROM<\/span> sys<\/span>\nLIKE<\/span> 'waits%'<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the output:<\/p>\n\n\n\n
\"mysql<\/figure>\n\n\n\n
Note that the SHOW TABLES<\/code> statement returns only the views that you have the privilege to access.<\/p>\n\n\n\n
MySQL Show View –  Using INFORMATION_SCHEMA database<\/h2>\n\n\n\n
The information_schema<\/code> database provides access to MySQL database metadata such as databases, tables, data types of columns, or privileges. The information schema is also known as a database dictionary or system catalog.<\/p>\n\n\n\n
To show the views of a database, you use the tables<\/code> table from the information_schema<\/code> database:<\/p>\n\n\n
SELECT<\/span> * \nFROM<\/span> information_schema.tables;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here’s the partial output:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
The columns that are relevant to the views are:<\/p>\n\n\n\n
    \n
  • The table_schema<\/code> column stores the schema or database of the view (or table).<\/li>\n\n\n\n
  • The table_name<\/code> column stores the name of the view (or table).<\/li>\n\n\n\n
  • The table_type<\/code> column stores the type of tables: BASE TABLE<\/code> for a table, VIEW<\/code> for a view, or SYSTEM VIEW<\/code> for an INFORMATION_SCHEMA<\/code> table.<\/li>\n<\/ul>\n\n\n\n
    For example, this query returns all views from the classicmodels<\/code> database:<\/p>\n\n\n
    SELECT<\/span> \n    table_name view_name\nFROM<\/span> \n    information_schema.tables \nWHERE<\/span> \n    table_type   = 'VIEW'<\/span> AND<\/span> \n    table_schema = 'classicmodels'<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    To find the views that match a pattern, you use the table_name<\/code> column. For example, this query finds all views whose names start with customer<\/code>:<\/p>\n\n\n
    SELECT<\/span> \n    table_name view_name\nFROM<\/span> \n    information_schema.tables \nWHERE<\/span> \n    table_type   = 'VIEW'<\/span> AND<\/span> \n    table_schema = 'classicmodels'<\/span> AND<\/span>\n    table_name   LIKE<\/span> 'customer%'<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    Summary<\/h2>\n\n\n\n
      \n
    • Use the SHOW FULL TABLE<\/code> with the type_type<\/code> VIEW<\/code> to return all views from the current database.<\/li>\n\n\n\n
    • Use the SHOW FULL TABLE FROM<\/code> (or IN<\/code>) statement to get all views in a specified database.<\/li>\n\n\n\n
    • Add the LIKE<\/code> clause to the SHOW FULL TABLE<\/code> statement to get the views that match a pattern.<\/li>\n\n\n\n
    • Query data from the table information_schema.tables<\/code> to get the views in a database.<\/li>\n<\/ul>\n
      \n\t
      \n\t\t
      Was this tutorial helpful? <\/div>\n\t\t
      \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
      \n\t\t
      \n\t\t\t
      <\/div>\n\t\t\t\n\t\t\t
Because the SHOW FULL TABLES<\/code> statement returns both tables and views, we need to add a WHERE<\/a><\/code> clause to obtain views only.<\/p>\n\n\n\n