VIEWS

The VIEWS table provides information about SQL views.

USE INFORMATION_SCHEMA;
DESC VIEWS;

The output is as follows:

+----------------------+--------------+------+------+---------+-------+
| Field                | Type         | Null | Key  | Default | Extra |
+----------------------+--------------+------+------+---------+-------+
| TABLE_CATALOG        | varchar(512) | NO   |      | NULL    |       |
| TABLE_SCHEMA         | varchar(64)  | NO   |      | NULL    |       |
| TABLE_NAME           | varchar(64)  | NO   |      | NULL    |       |
| VIEW_DEFINITION      | longtext     | NO   |      | NULL    |       |
| CHECK_OPTION         | varchar(8)   | NO   |      | NULL    |       |
| IS_UPDATABLE         | varchar(3)   | NO   |      | NULL    |       |
| DEFINER              | varchar(77)  | NO   |      | NULL    |       |
| SECURITY_TYPE        | varchar(7)   | NO   |      | NULL    |       |
| CHARACTER_SET_CLIENT | varchar(32)  | NO   |      | NULL    |       |
| COLLATION_CONNECTION | varchar(32)  | NO   |      | NULL    |       |
+----------------------+--------------+------+------+---------+-------+
10 rows in set (0.00 sec)

Create a view and query the VIEWS table:

CREATE VIEW test.v1 AS SELECT 1;
SELECT * FROM VIEWS\G

The output is as follows:

*************************** 1. row ***************************
       TABLE_CATALOG: def
        TABLE_SCHEMA: test
          TABLE_NAME: v1
     VIEW_DEFINITION: SELECT 1
        CHECK_OPTION: CASCADED
        IS_UPDATABLE: NO
             DEFINER: root@127.0.0.1
       SECURITY_TYPE: DEFINER
CHARACTER_SET_CLIENT: utf8mb4
COLLATION_CONNECTION: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

Fields in the VIEWS table are described as follows:

• TABLE_CATALOG: The name of the catalog to which the view belongs. This value is always def.
• TABLE_SCHEMA: The name of the schema to which the view belongs.
• TABLE_NAME: The view name.
• VIEW_DEFINITION: The definition of view, which is made by the SELECT statement when the view is created.
• CHECK_OPTION: The CHECK_OPTION value. The value options are NONE, CASCADE, and LOCAL.
• IS_UPDATABLE: Whether UPDATE/INSERT/DELETE is applicable to the view. In TiDB, the value is always NO.
• DEFINER: The name of the user who creates the view, which is in the format of 'user_name'@'host_name'.
• SECURITY_TYPE: The value of SQL SECURITY. The value options are DEFINER and INVOKER.
• CHARACTER_SET_CLIENT: The value of the character_set_client session variable when the view is created.
• COLLATION_CONNECTION: The value of the collation_connection session variable when the view is created.