SHOW VIEWS
說明
SHOW VIEWS 陳述式傳回選用指定的資料庫的所有檢視。此外，此陳述式的輸出可以透過選用的比對樣式進行篩選。如果未指定資料庫，則會從目前的資料庫傳回檢視。如果指定的資料庫是全域暫時檢視資料庫，我們將列出全域暫時檢視。請注意，此指令也會列出區域暫時檢視，而不論指定的資料庫為何。
語法
SHOW VIEWS [ { FROM | IN } database_name ] [ LIKE regex_pattern ]
參數
-
{ FROM
|IN } database_name指定要列出檢視的資料庫名稱。
-
regex_pattern
指定用於篩選不需要的檢視的正規表示式樣式。
- 除了
*和|字元外，樣式會像正規表示式一樣運作。 - 單獨的
*會比對 0 個或更多字元，而|用於分隔多個不同的正規表示式，其中任何一個都可以比對。 - 在處理之前，會修剪輸入樣式中的前導和尾隨空白。樣式比對不分大小寫。
- 除了
範例
-- Create views in different databases, also create global/local temp views.
CREATE VIEW sam AS SELECT id, salary FROM employee WHERE name = 'sam';
CREATE VIEW sam1 AS SELECT id, salary FROM employee WHERE name = 'sam1';
CREATE VIEW suj AS SELECT id, salary FROM employee WHERE name = 'suj';
USE userdb;
CREATE VIEW user1 AS SELECT id, salary FROM default.employee WHERE name = 'user1';
CREATE VIEW user2 AS SELECT id, salary FROM default.employee WHERE name = 'user2';
USE default;
CREATE GLOBAL TEMP VIEW temp1 AS SELECT 1 AS col1;
CREATE TEMP VIEW temp2 AS SELECT 1 AS col1;
-- List all views in default database
SHOW VIEWS;
+-------------+------------+--------------+
| namespace | viewName | isTemporary |
+-------------+------------+--------------+
| default | sam | false |
| default | sam1 | false |
| default | suj | false |
| | temp2 | true |
+-------------+------------+--------------+
-- List all views from userdb database
SHOW VIEWS FROM userdb;
+-------------+------------+--------------+
| namespace | viewName | isTemporary |
+-------------+------------+--------------+
| userdb | user1 | false |
| userdb | user2 | false |
| | temp2 | true |
+-------------+------------+--------------+
-- List all views in global temp view database
SHOW VIEWS IN global_temp;
+-------------+------------+--------------+
| namespace | viewName | isTemporary |
+-------------+------------+--------------+
| global_temp | temp1 | true |
| | temp2 | true |
+-------------+------------+--------------+
-- List all views from default database matching the pattern `sam*`
SHOW VIEWS FROM default LIKE 'sam*';
+-----------+------------+--------------+
| namespace | viewName | isTemporary |
+-----------+------------+--------------+
| default | sam | false |
| default | sam1 | false |
+-----------+------------+--------------+
-- List all views from the current database matching the pattern `sam|suj｜temp*`
SHOW VIEWS LIKE 'sam|suj|temp*';
+-------------+------------+--------------+
| namespace | viewName | isTemporary |
+-------------+------------+--------------+
| default | sam | false |
| default | suj | false |
| | temp2 | true |
+-------------+------------+--------------+