REPEAT

Runs a query, a block of queries or a script again and again, in the foreground, to watch something change. The parts always come in this order: REPEAT [<target>] EVERY <interval> [FOR <duration> | COUNT <iterations>] [TO <file> [AS CSV|JSON|TEXT]]. The target is nothing or / (the last query), BEGIN <query>; ... END (a block of queries run in order), @<script> or LIB RUN <script>. The first iteration starts at once; EVERY is the wait after each iteration ends, so iterations never overlap. Durations are a whole number followed by s, m or h (10s, 5m, 2h). Only queries can be repeated. Ctrl+C stops it.

Arguments

[<target>] EVERY <interval> [FOR <duration> | COUNT <iterations>] [TO <file> [AS CSV|JSON|TEXT]], where <target> is nothing or / (the last query), BEGIN <query>; <query>; ... END, @<script> [name=value ...] or LIB RUN <script> [name=value ...]; <interval> and <duration> are a positive whole number immediately followed by s, m or h (minimum 1s); <iterations> is a positive whole number; FOR and COUNT cannot be combined

Examples

SELECT COUNT(*) FROM JOBS WHERE STATUS = 'RUNNING';
REPEAT EVERY 5s;
REPEAT / EVERY 10s COUNT 6;
REPEAT
BEGIN
    SELECT COUNT(*) FROM ORDERS;
    SELECT COUNT(*) FROM INVOICES;
    SELECT COUNT(*) FROM PAYMENTS WHERE STATUS = 'FAILED';
END
EVERY 10s;
REPEAT @monitor.sql EVERY 10s FOR 30m;
REPEAT LIB RUN monitor.sql EVERY 1m COUNT 60;
REPEAT
BEGIN
    SELECT STATUS, COUNT(*) AS CNT FROM PROCESS_QUEUE GROUP BY STATUS;
END
EVERY 10s
FOR 1h
TO queue-monitor.csv AS CSV;

Notes

Runs a query, a block of queries or a script again and again, in the foreground, to watch something change: REPEAT [<target>] EVERY <interval> [FOR <duration> | COUNT <iterations>] [TO <file> [AS CSV|JSON|TEXT]]. The parts always come in this order: what to repeat, how often, for how long, and where to record it.

The target is one of:

• nothing, or /: the last query, the one / runs again. REPEAT EVERY 10s and REPEAT / EVERY 10s are the same command. It is the last SQL query typed at the prompt, not the last command: after SELECT ...; then DESC JOBS;, REPEAT EVERY 5s repeats the SELECT. Written in a script, it is the last query of that script, executed before the REPEAT: never a query typed at the prompt before the script started, nor a query of another script it called. A script with no query before the REPEAT is refused. In both cases the most recent query is the one used: if it changes data, REPEAT refuses it.
• BEGIN <query>; <query>; ... END: a block of queries, each ending with ;, typed on one line or over several lines. The block ends at the END word that follows the last query.
• @<script> [name=value ...]: a Script, found and run exactly as @ finds and runs it.
• LIB RUN <script> [name=value ...]: a Scripts Library script, run exactly as LIB RUN runs it.

One iteration runs the whole target: the one query, every query of the block, or the whole script. Its statements run one after another, in their written order, never at the same time. The first iteration starts immediately. EVERY is the wait after an iteration has completed: if the queries take 3 seconds and the command says EVERY 10s, an iteration starts about every 13 seconds. Two iterations never overlap.

REPEAT runs in the foreground: it keeps the prompt until it ends, and a statement written after it (in a script, or on the same line) runs only once it has ended. Several REPEAT commands are never parallel monitors: to watch three queries together, put them in one BEGIN ... END block or in one script. Press Ctrl+C to stop it, during a query or during the wait: BroadSQL stops the REPEAT, returns to the prompt and stays connected. When the REPEAT was started by a script, Ctrl+C stops that script too.

Durations

EVERY and FOR take a duration: a positive whole number immediately followed by its unit.

The unit may be written in capitals (10S). Nothing else is accepted, and each refused form is explained:

• no space between the number and the unit: 10s, not 10 s;
• no milliseconds (500ms): the shortest interval is EVERY 1s;
• no decimals (0.5s, 1.5m): write 90s;
• no combined units (1h30m): write 90m;
• no days (1d): write 24h;
• no zero and no negative value.

EVERY is mandatory, there is no default interval.

How long it runs

Without FOR or COUNT, REPEAT runs until Ctrl+C or an error.

COUNT <iterations>, a positive whole number, runs at most that many complete iterations: EVERY 2s COUNT 3 runs the target three times, then ends without a last wait.

FOR <duration> is measured from the start of the first iteration. A new iteration starts only before the deadline: with EVERY 10s FOR 30s and fast queries, iterations start at about 0, 10 and 20 seconds. An iteration still running when the deadline passes is never interrupted: it completes, then REPEAT ends.

FOR and COUNT cannot be combined.

Queries only

REPEAT is for monitoring, so it applies a query-only restriction: it repeats SELECT, WITH, SHOW and EXPLAIN statements, and calls of scripts (@, LIB RUN) that themselves contain only such queries. INSERT, UPDATE, DELETE, MERGE, CALL, DDL, a query containing INTO or FOR UPDATE, and every BroadSQL command (CONNECT, DUMP, LET, SET...) are refused, whatever the target: the last query, the block, the script and the scripts it calls. What can be checked before the start is checked then, and every statement is checked again just before it runs, so nothing that is refused ever reaches the database. A REPEAT inside a repeated target is refused too: REPEAT cannot be nested. This restriction is about statements, not a guarantee that the database never changes: a query can call a function that has side effects (the next value of a sequence, or a function of the database product), and functions are not examined.

Errors

The first error ends the REPEAT: the error is shown as usual, the rest of the iteration is not run, no other iteration starts, and the prompt returns. A repeated script stops at its first error whatever its ON ERROR setting.

Display

Each iteration starts with a line giving its number and time (=== Repeat #3 at 2026-10-07 21:10:13 ===). The results follow, displayed as usual, one after another; the screen is never cleared. In a block of several queries, each result is preceded by its position and query ([2/3] SELECT ...). After each iteration, its duration and the next wait are shown.

Monitoring output

TO <file> [AS CSV|JSON|TEXT] also records every iteration in a file, in addition to the screen, which keeps showing everything. Each iteration is appended: nothing already in the file is ever overwritten. A relative file name is in the export folder (DefaultFolder); without AS, the extension (.csv, .json, .txt) gives the format, and without an extension, AS adds it.

• CSV: one row per result row, separated by the CsvSeparator setting, with a header when the file is new. The first column, TIMESTAMP, is the time the iteration started, so the file is a timeline without changing the query. When the result already has a TIMESTAMP column, the timestamp column is named REPEAT_TIMESTAMP, then REPEAT_TIMESTAMP_2, REPEAT_TIMESTAMP_3... (the first name the result does not use); the result's own columns are never renamed. An existing file is appended to only when it has the same columns.
• JSON: JSON Lines, one object per result row and per line, with the same timestamp first.
• TEXT: a readable log: the iteration number and time, then each query and its rows, tab separated.

CSV and JSON hold one table, so they need exactly one tabular result per iteration: a block or script with several queries is refused with these formats, rather than mixing unrelated columns in one file. TEXT records any number of results. Only complete results are recorded: a result cut at MaxRowsOnScreen stops the REPEAT.

In a script and in the Editor

A REPEAT block written in a script is read as one statement, its inner ; included, by @, LIB RUN and the BroadSQL Editor's Run. In the Editor, which shows a run's output when it ends, REPEAT needs FOR or COUNT.

A reusable monitoring script

A file monitor.sql in the Scripts Library:

SELECT COUNT(*) AS OPEN_ORDERS FROM ORDERS WHERE STATUS = 'OPEN';
SELECT COUNT(*) AS FAILED_INVOICES FROM INVOICES WHERE STATUS = 'FAILED';
SELECT MAX(CREATED_AT) AS LAST_ORDER FROM ORDERS;

REPEAT LIB RUN monitor.sql EVERY 10s FOR 30m; (or REPEAT @monitor.sql EVERY 10s FOR 30m;) runs the three queries in order every 10 seconds for 30 minutes; each complete run of the script is one iteration.

See Running Scripts for how @ and LIB RUN find a script, and Export files for the CSV and JSON formats.

Last modified in release 5.4.5.