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_type_plural> [ LIKE '<pattern>' ] [ IN <scope_object_type> [ <scope_object_name> ] ]
Copy

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

Account Operations

Session / User Operations:

Account Objects:

Database Objects:

Classes:

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 at least one access privilege.

  • Executing a SHOW command for schema-level objects only returns an object if the current role also has at least one 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:

    • 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.

    Note

    Using SHOW commands without an IN clause in a database context can result in fewer than expected results.

    Objects with the same name will only display once if no IN clause is used. For example, if you have table t1 in schema1 and t1 in schema2 and they are both in scope of the database context you’ve specified (i.e., the database you’ve selected is the parent of schema1 and schema2), then SHOW TABLES will only display one of the t1 tables.

  • Columns that start with the prefix is_ return either Y (yes) or N (no).

  • 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 Snowflake 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.

Language: English