Lists the existing objects for the specified object type. The output includes metadata for the objects, including:
Common properties (name, creation timestamp, owning role, comment, etc.)
SHOW <object_type_plural> [ LIKE '<pattern>' ] [ IN <scope_object_type> [ <scope_object_name> ] ]
For specific syntax, usage notes, and examples, see:
Session / User Operations:
Account Object Types:
Database Object Types:
General Usage Notes¶
SHOW commands do not require a running warehouse to execute.
SHOW commands only return objects for which the current user’s current role has been granted the necessary access privileges. For example:
The SHOW DATABASES output includes the databases for which the user’s role has the OWNERSHIP or USAGE privilege.
The SHOW SCHEMAS output includes the schemas for which the user’s role has the OWNERSHIP or USAGE privilege.
The SHOW TABLES output includes the tables for which the user’s role has any privileges. The role must also have the USAGE privilege on the parent database and schema.
The MANAGE GRANTS access privilege implicitly allows its holder to see every object in the account. By default, only the account administrator (users with the ACCOUNTADMIN role) and security administrator (users with the SECURITYADMIN role) have the MANAGE GRANTS privilege.
The output of most SHOW commands can be controlled using the following clauses:
LIKEclause can be used to filter the list of objects returned by name.
The database object types provide an additional, optional
INclause, which can be used to set the scope of the command to either a specific schema or database, or across the entire account.
Using SHOW commands without an
INclause in a database context can result in fewer than expected results.
Objects with the same name will only display once if no
INclause is used. For example, if you have table
schema2and they are both in scope of the database context you’ve specified (i.e., the database you’ve selected is the parent of
schema2), then SHOW TABLES will only display one of the
The command does not require a running warehouse to execute.
The command returns a maximum of 10K records for the specified object type, as dictated by the access privileges for the role used to execute the command; any records above the 10K limit are not returned, even with a filter applied.
To view results for which more than 10K records exist, query the corresponding view (if one exists) in the Information Schema.
To post-process the output of this command, you can use the RESULT_SCAN function, which treats the output as a table that can be queried.