SQL Library and Scripts

BroadSQL keeps two separate catalogs of reusable files, browsed, searched, edited and run with the same tooling:

• the SQL library (LIB * commands): one saved, optionally parameterized query per file;
• the scripts catalog (SCRIPT * commands): a mix of BroadSQL commands and raw SQL per file, the same shape already supported by the @<file> mechanism.

Both are plain folders of text files on disk, configured by the SqlLib and Scripts keys in BroadSQL.ini (see Application settings), and both share the same metadata format, instance/environment scoping, and safe delete behavior described below.

Metadata header

Any entry can start with a short header of -- @key: value comment lines. A file with none of these lines is simply untagged, so nothing already in either catalog needs to change to keep working.

-- @description: Monthly revenue by country
-- @instance: MYSAP
-- @environment: PROD
-- @tags: finance, monthly, revenue
-- @alias: rev
-- @status: stable
select country, sum(amount) from revenue where year = %1 group by country;

The header is skipped automatically before a query is sent to the database, so it never interferes with %N parameter substitution or execution.

Browsing and searching

LIB LIST/SCRIPT LIST render a grid, one row per entry: File, Alias, Description, Instance, Environment, Tags, Status, Modified, and (library only) Params, the count of distinct %1 to %9 placeholders found in the file.

By default, the list is scoped to both the current connection's instance and environment: an entry shows only if it matches (or is tagged ALL, or is untagged/NONE) on both dimensions independently. An entry tagged for a different, specific instance or environment is hidden. LIB LIST ALL/SCRIPT LIST ALL lifts both scoping dimensions at once and shows every entry. An optional search term still filters by a case-insensitive substring of the file name, exactly as before.

LIB LIST;
LIB LIST revenue;
LIB LIST ALL;

LIB FIND <term>/SCRIPT FIND <term> search full text instead: every entry whose content contains <term> anywhere (the metadata header included, so a description or tag matches too) is shown, with the actual matching line printed underneath its grid row so it is clear why it matched without opening the file. Unlike LIST, FIND always searches the whole catalog regardless of instance or environment.

LIB FIND revenue;

Running

LIB RUN <name> [param1 param2 ...] runs a saved query. <name> is matched against the file's relative path, a declared @alias, or (if unambiguous) its bare file name; the .sql extension is added automatically if nothing else matched. Positional placeholders %1 to %9 in the query are replaced, in order, by the extra arguments given; if a placeholder is still present after substitution, BroadSQL reports that not enough parameters were given and does not run the query. Giving more arguments than the query actually uses is reported too, but the query still runs.

LIB RUN revenue_by_country.sql CH 2026;
LIB RUN rev CH 2026;

If the entry's @instance and/or @environment metadata does not match the current connection's instance/environment, BroadSQL prints one warning line per mismatched dimension first, then runs it anyway: the tags document intent, they do not enforce a hard rule.

SCRIPT RUN <name> resolves <name> the same way, then runs every statement in the file exactly like @<path> already does, including nested BroadSQL commands. There is no %1 to %9 substitution for scripts: a script is an arbitrary sequence of statements, not a single parameterized query.

Editing

LIB EDIT <name>/SCRIPT EDIT <name> (Windows only) opens the entry in Notepad and saves it back into the catalog when Notepad closes. If <name> does not already exist, the file Notepad opens is not empty: it starts pre-filled with a metadata skeleton, ready to fill in.

-- @description:
-- @instance: NONE
-- @environment: NONE
-- @tags:
-- @alias:
-- @status: draft

Deleting safely

LIB DEL <name>/SCRIPT DEL <name> ask for a y/n confirmation, then move the file to a visible archives/ subfolder of the catalog instead of deleting it. Nothing is purged automatically. Two ways to bring a file back:

• LIB UNDO/SCRIPT UNDO: restores whichever entry was archived most recently, no argument needed.
• LIB RESTORE <name>/SCRIPT RESTORE <name>: restores a specific archived entry by its original name.

Both refuse, rather than overwrite, if a live entry already occupies that name again. Browse what is archived with LIB LIST ARCHIVES/SCRIPT LIST ARCHIVES; archived entries never appear in an ordinary LIST/FIND.

Checking consistency

LIB LINT/SCRIPT LINT (optionally followed by a single entry's name) reports, without changing anything: an @instance or @environment id that does not match any instance/environment registered in the CDF (checked independently); two entries declaring the same @alias; and, library entries only, a %N sequence with a gap (e.g. a query using %1 and %3 but not %2).

Not supported (yet)

Related pages

• Command reference: LIB *, generated from each command's own code, always up to date.
• Application settings: the SqlLib/Scripts BroadSQL.ini keys.
• Script files (@<file>) and the file macro (<@file>) are documented on the Features page, "Automation and scripting".