SHOW DATABASES or SHOW SCHEMAS lists all of the databases defined in the metastore. The uses of SCHEMAS and DATABASES are interchangeable – they mean the same thing.
The optional LIKE clause allows the list of databases to be filtered using a regular expression. Wildcards in the regular expression can only be ‘’ for any character(s) or ‘|’ for a choice. Examples are ‘employees’, 'emp’, ‘emp*|*ees’, all of which will match the database named ‘employees’.
- 通配符只有两种： * 和 |
- * 表示所有
- | 表示或
SHOW DATABASES [LIKE 'identifier_with_wildcards'];
SHOW TABLESlists all the base tables and views in the current database (or the one explicitly named using the
INclause) with names matching the optional regular expression.
Wildcards in the regular expression can only be ‘*’ for any character(s) or ‘|’ for a choice.
SHOW TABLES [IN database_name] ['identifier_with_wildcards'];
SHOW VIEWS; -- show all views in the current database
SHOW PARTITIONS lists all the existing partitions for a given base table. Partitions are listed in alphabetical order.
It is also possible to specify parts of a partition specification to filter the resulting list.
SHOW PARTITIONS table_name;
SHOW TABLE EXTENDED will list information for all tables matching the given regular expression.
Users cannot use regular expression for table name if a partition specification is present.
SHOW TABLE EXTENDED [IN|FROM database_name] LIKE 'identifier_with_wildcards' [PARTITION(partition_spec)];
The first form lists all of the table properties for the table in question, one per row separated by tabs.
The second form of the command prints only the value for the property that’s being asked for.
SHOW TBLPROPERTIES tblname;
- “acquired” – the requestor holds the lock
- “waiting” – the requestor is waiting for the lock
- “aborted” – the lock has timed out but has not yet been cleaned up
- “exclusive” – no one else can hold the lock at the same time (obtained mostly by DDL operations such as drop table)
- “shared_read” – any number of other shared_read locks can lock the same resource at the same time (obtained by reads; confusingly, an insert operation also obtains a shared_read lock)
- “shared_write” – any number of shared_read locks can lock the same resource at the same time, but no other shared_write locks are allowed (obtained by update and delete)
SHOW LOCKS ;
It returns a list of all currently open and aborted transactions in the system, including this information: