DYNAMIC_TABLES function: New default for maximum number of rows returned (Preview)¶

The DYNAMIC_TABLES function behaves as follows:

Before the change:

By default, the function returns all rows in an unsorted order when RESULT_LIMIT is not specified. For example, if an account has 10,000 dynamic tables, the function returns 10,000 rows.

After the change:

By default, the function returns 100 rows and the results are sorted by the dynamic table’s last completed refresh state in the following order, unless specified otherwise using the RESULT_LIMIT argument.

1. FAILED

2. UPSTREAM_FAILED

3. SKIPPED

4. SUCCEEDED

5. CANCELED

To sort by a different order, you must provide a large enough RESULT_LIMIT value (for example, the maximum value of a signed integer). As long as RESULT_LIMIT exceeds the total number of dynamic tables in the account, the results can be sorted using an ORDER BY clause.

To apply a filter on the results, also specify a large enough RESULT_LIMIT value for the filter to be applied on all dynamic tables.

Examples:

The following example sorts by a different order of name and returns 100 rows:

SELECT * FROM TABLE(INFORMATION_SCHEMA.DYNAMIC_TABLES(result_limit => <max_value>)) ORDER BY name ASC LIMIT 100 ;

Copy

The following example sorts by a different order of name and returns all rows:

SELECT * FROM TABLE(INFORMATION_SCHEMA.DYNAMIC_TABLES(result_limit => <max_value>)) ORDER BY name ASC ;

Copy

The following example filters for all dynamic tables with 1-minute target lag, uses the default sort, and returns all rows:

SELECT * FROM TABLE(INFORMATION_SCHEMA.DYNAMIC_TABLES(result_limit => <max_value>)) WHERE TARGET_LAG_SEC = 60 ;

Copy

Ref: 1928