0001 ---
0002 layout: global
0003 title: SHOW VIEWS
0004 displayTitle: SHOW VIEWS
0005 license: |
0006 Licensed to the Apache Software Foundation (ASF) under one or more
0007 contributor license agreements. See the NOTICE file distributed with
0008 this work for additional information regarding copyright ownership.
0009 The ASF licenses this file to You under the Apache License, Version 2.0
0010 (the "License"); you may not use this file except in compliance with
0011 the License. You may obtain a copy of the License at
0012
0013 http://www.apache.org/licenses/LICENSE-2.0
0014
0015 Unless required by applicable law or agreed to in writing, software
0016 distributed under the License is distributed on an "AS IS" BASIS,
0017 WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
0018 See the License for the specific language governing permissions and
0019 limitations under the License.
0020 ---
0021
0022 ### Description
0023
0024 The `SHOW VIEWS` statement returns all the views for an optionally specified database.
0025 Additionally, the output of this statement may be filtered by an optional matching
0026 pattern. If no database is specified then the views are returned from the
0027 current database. If the specified database is global temporary view database, we will
0028 list global temporary views. Note that the command also lists local temporary views
0029 regardless of a given database.
0030
0031 ### Syntax
0032 ```sql
0033 SHOW VIEWS [ { FROM | IN } database_name ] [ LIKE regex_pattern ]
0034 ```
0035
0036 ### Parameters
0037 * **{ FROM `|` IN } database_name**
0038
0039 Specifies the database name from which views are listed.
0040
0041 * **regex_pattern**
0042
0043 Specifies the regular expression pattern that is used to filter out unwanted views.
0044
0045 * Except for `*` and `|` character, the pattern works like a regular expression.
0046 * `*` alone matches 0 or more characters and `|` is used to separate multiple different regular expressions,
0047 any of which can match.
0048 * The leading and trailing blanks are trimmed in the input pattern before processing. The pattern match is case-insensitive.
0049
0050 ### Examples
0051 ```sql
0052 -- Create views in different databases, also create global/local temp views.
0053 CREATE VIEW sam AS SELECT id, salary FROM employee WHERE name = 'sam';
0054 CREATE VIEW sam1 AS SELECT id, salary FROM employee WHERE name = 'sam1';
0055 CREATE VIEW suj AS SELECT id, salary FROM employee WHERE name = 'suj';
0056 USE userdb;
0057 CREATE VIEW user1 AS SELECT id, salary FROM default.employee WHERE name = 'user1';
0058 CREATE VIEW user2 AS SELECT id, salary FROM default.employee WHERE name = 'user2';
0059 USE default;
0060 CREATE GLOBAL TEMP VIEW temp1 AS SELECT 1 AS col1;
0061 CREATE TEMP VIEW temp2 AS SELECT 1 AS col1;
0062
0063 -- List all views in default database
0064 SHOW VIEWS;
0065 +-------------+------------+--------------+
0066 | namespace | viewName | isTemporary |
0067 +-------------+------------+--------------+
0068 | default | sam | false |
0069 | default | sam1 | false |
0070 | default | suj | false |
0071 | | temp2 | true |
0072 +-------------+------------+--------------+
0073
0074 -- List all views from userdb database
0075 SHOW VIEWS FROM userdb;
0076 +-------------+------------+--------------+
0077 | namespace | viewName | isTemporary |
0078 +-------------+------------+--------------+
0079 | userdb | user1 | false |
0080 | userdb | user2 | false |
0081 | | temp2 | true |
0082 +-------------+------------+--------------+
0083
0084 -- List all views in global temp view database
0085 SHOW VIEWS IN global_temp;
0086 +-------------+------------+--------------+
0087 | namespace | viewName | isTemporary |
0088 +-------------+------------+--------------+
0089 | global_temp | temp1 | true |
0090 | | temp2 | true |
0091 +-------------+------------+--------------+
0092
0093 -- List all views from default database matching the pattern `sam*`
0094 SHOW VIEWS FROM default LIKE 'sam*';
0095 +-----------+------------+--------------+
0096 | namespace | viewName | isTemporary |
0097 +-----------+------------+--------------+
0098 | default | sam | false |
0099 | default | sam1 | false |
0100 +-----------+------------+--------------+
0101
0102 -- List all views from the current database matching the pattern `sam|suj｜temp*`
0103 SHOW VIEWS LIKE 'sam|suj|temp*';
0104 +-------------+------------+--------------+
0105 | namespace | viewName | isTemporary |
0106 +-------------+------------+--------------+
0107 | default | sam | false |
0108 | default | suj | false |
0109 | | temp2 | true |
0110 +-------------+------------+--------------+
0111 ```
0112
0113 ### Related statements
0114 * [CREATE VIEW](sql-ref-syntax-ddl-create-view.html)
0115 * [DROP VIEW](sql-ref-syntax-ddl-drop-view.html)
0116 * [CREATE DATABASE](sql-ref-syntax-ddl-create-database.html)
0117 * [DROP DATABASE](sql-ref-syntax-ddl-drop-database.html)
