Categories:
DDL Commands (by Category)

SHOW <objects>

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.)
  • Object-specific properties
See also:
CREATE <object> , DESCRIBE <object>

Syntax

SHOW <object_types> [ LIKE '<pattern>' ] [ IN <scope_object_type> [ <scope_object_name> ] ]

For specific syntax, usage notes, and examples, see:

Account Operations

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 system administrator (users with the SYSADMIN role) have the MANAGE GRANTS privilege.
  • The output of most SHOW commands can be controlled using the following clauses:

    • The optional LIKE clause can be used to filter the list of objects returned by name.
    • The database object types provide an additional, optional IN clause, which can be used to set the scope of the command to either a specific schema or database, or across the entire account.
  • 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.