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 alwaysdef
.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 theSELECT
statement when the view is created.CHECK_OPTION
: TheCHECK_OPTION
value. The value options areNONE
,CASCADE
, andLOCAL
.IS_UPDATABLE
: WhetherUPDATE
/INSERT
/DELETE
is applicable to the view. In TiDB, the value is alwaysNO
.DEFINER
: The name of the user who creates the view, which is in the format of'user_name'@'host_name'
.SECURITY_TYPE
: The value ofSQL SECURITY
. The value options areDEFINER
andINVOKER
.CHARACTER_SET_CLIENT
: The value of thecharacter_set_client
session variable when the view is created.COLLATION_CONNECTION
: The value of thecollation_connection
session variable when the view is created.