SHOW Commands: Pagination Support

Attention

This behavior change is in the 2023_05 bundle.

For the current status of the bundle, refer to Bundle History.

In the current release, the behavior of these commands changes as follows:

Previously:

These commands do not support limiting the row output or pagination of the results.

Currently:

These commands support pagination of the output using a LIMIT … FROM clause:

SHOW <domain_plural> [ LIMIT <rows> [ FROM '<name_string>' ] ]
Copy

The SHOW GRANTS command only supports the LIMIT clause. For example:

SHOW GRANTS [ LIMIT <rows> ]
Copy

Where:

domain_plural

Use one of the following plural forms of the object domain:

  • APPLICATION ROLES

  • DATABASE ROLES

  • ROLES

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 night be less than the specified limit (e.g. 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. In addition, 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).

For example:

SHOW APPLICATION ROLES IN APPLICATION myapp LIMIT 10 FROM 'app_role2';
Copy

The statement returns up to ten application roles in the application named myapp after the first application role named app_role2.

Ref: 1080