SHOW INDEXES¶

Syntax¶

SHOW [ TERSE ] INDEXES
  [ LIKE '<pattern>' ]
  [ IN { ACCOUNT | DATABASE [ <database_name> ] | SCHEMA [ <schema_name> ] | TABLE | TABLE <table_name> } ]
  [ STARTS WITH '<name_string>' ]
  [ LIMIT <rows> [ FROM '<name_string>' ] ]

Parameters¶

TERSE

Returns only a subset of the output columns:

• created_on

• name

• kind

• database_name

• schema_name

LIKE 'pattern'

Optionally filters the command output by object name. The filter uses case-insensitive pattern matching, with support for SQL wildcard characters (% and _).

For example, the following patterns return the same results:

... LIKE '%testing%' ...

... LIKE '%TESTING%' ...

. Default: No value (no filtering is applied to the output).

IN { ACCOUNT | DATABASE [ database_name ] | SCHEMA [ schema_name ] | TABLE | TABLE table_name }

Filters the output by the specified database, schema, table, or account.

If you specify the keyword ACCOUNT, then the command retrieves records for all schemas in all databases of the current account.

If you specify the keyword DATABASE, then:

• If you specify a db_name, then the command retrieves records for all schemas of the specified database.

• If you do not specify a db_name, then:

  • If there is a current database, then the command retrieves records for all schemas in the current database.

  • If there is no current database, then the command retrieves records for all databases and schemas in the account.

If you specify the keyword SCHEMA, then:

• If you specify a qualified schema name (e.g. my_database.my_schema), then the command retrieves records for the specified database and schema.

• If you specify an unqualified schema_name, then:

  • If there is a current database, then the command retrieves records for the specified schema in the current database.

  • If there is no current database, then the command displays the error SQL compilation error: Object does not exist, or operation cannot be performed.

• If you do not specify a schema_name, then:

  • If there is a current database, then:

    • If there is a current schema, then the command retrieves records for the current schema in the current database.

    • If there is no current schema, then the command retrieves records for all schemas in the current database.

  • If there is no current database, then the command retrieves records for all databases and all schemas in the account.

If you specify the keyword TABLE without a table_name, then:

• If there is a current database, then:

  • If there is a current schema, then the command retrieves records for the current schema in the current database.

  • If there is no current schema, then the command retrieves records for all schemas in the current database.

• If there is no current database, then the command retrieves records for all databases and all schemas in the account.

If you specify a <table_name> (with or without the keyword TABLE), then:

• If you specify a fully-qualified <table_name> (e.g. my_database_name.my_schema_name.my_table_name), then the command retrieves all records for the specified table.

• If you specify a schema-qualified <table_name> (e.g. my_schema_name.my_table_name), then:

  • If a current database exists, then the command retrieves all records for the specified table.

  • If no current database exists, then the command displays an error similar to Cannot perform SHOW <object_type>. This session does not have a current database....

• If you specify an unqualified <table_name>, then:

  • If a current database and current schema exist, then the command retrieves records for the specified table in the current schema of the current database.

  • If no current database exists or no current schema exists, then the command displays an error similar to: SQL compilation error: <object> does not exist or not authorized..

Default: Depends on whether the session currently has a database in use:

• Database: DATABASE is the default (i.e. the command returns the objects you have privileges to view in the database).

• No database: ACCOUNT is the default (i.e. the command returns the objects you have privileges to view in your account).

STARTS WITH 'name_string'

Optionally filters the command output based on the characters that appear at the beginning of the object name. The string must be enclosed in single quotes and is case-sensitive.

For example, the following strings return different results:

... STARTS WITH 'B' ...

... STARTS WITH 'b' ...

. Default: No value (no filtering is applied to the output)

LIMIT rows [ FROM 'name_string' ]

Optionally limits the maximum number of rows returned, while also enabling “pagination” of the results. The actual number of rows returned might be less than the specified limit. For example, the number of existing objects is less than the specified limit.

The optional FROM 'name_string' subclause effectively serves as a “cursor” for the results. This enables fetching the specified number of rows following the first row whose object name matches the specified string:

• The string must be enclosed in single quotes and is case-sensitive.

• The string does not have to include the full object name; partial names are supported.

Default: No value (no limit is applied to the output)

Note

For SHOW commands that support both the FROM 'name_string' and STARTS WITH 'name_string' clauses, you can combine both of these clauses in the same statement. However, both conditions must be met or they cancel out each other and no results are returned.

In addition, objects are returned in lexicographic order by name, so FROM 'name_string' only returns rows with a higher lexicographic value than the rows returned by STARTS WITH 'name_string'.

For example:

• ... STARTS WITH 'A' LIMIT ... FROM 'B' would return no results.

• ... STARTS WITH 'B' LIMIT ... FROM 'A' would return no results.

• ... STARTS WITH 'A' LIMIT ... FROM 'AB' would return results (if any rows match the input strings).

Usage Notes¶

• This command can only be used to drop a secondary index. To drop an index used to enforce unique and/or foreign key constraint(s), use the ALTER TABLE command instead.

• To use a secondary index on a hybrid table, the SELECT privilege must be granted to that table.

Output¶