On this page

Command reference

Every documented BroadSQL command, 96 in 13 categories. Each name links to its reference page: syntax, arguments, examples and details. Generated from the source code at release time.

Not a command but part of the language: Syntax and shortcuts (/, / PROD, @script, list sources such as <@clipboard>, ${name}) and Script directives (-- @param and the other header lines). The search above finds them too. Short on time? See the cheat sheet.

Connections

Connect to databases and manage saved connections.

Command	Description	Aliases
`ADD CONNECTION`	Creates a database connection in the CDF, through an interactive wizard	ADD CO, ADDCO, AD CO, ADCO
`CONNECT`	Opens a connection to a database, or sets the API session context with CONNECT API	OPEN, CONN, CON
`DEL CONNECTION`	Deletes a database connection by making it inactive; it can be reactivated	DEL CO, DELCO, DE CO, DECO
`DISCONNECT`	Closes the current connection; returns to the previous one with RememberLastConnection=TRUE, otherwise to BroadSQL>	BYE, CLOSE
`DUPLICATE CONNECTION`	Creates a database connection from a copy of an existing one, through the connection wizard	DUP CO, DUPCO
`EDIT CONNECTION`	Changes a database connection of the CDF, through an interactive wizard	ED CO, EDCO
`LOGOUT`	Ends the authenticated session: closes every connection and asks for the master password again	LOGOFF
`PING`	Tests a database connection without opening it	CHECK
`REACTIVATE CONNECTION`	Reactivates a deleted (inactive) database connection, unchanged	REACT CO, REACO
`SHOW ALL CONNECTIONS`	Lists the active database connections of the CDF	SH ALL CO, SH AL CO, SHALLCO, SHALCO
`SHOW CONNECTION`	Shows the details of a connection: name, type, URL, user, Database Group and Environment	SH CO, SHCO
`SHOW INACTIVE CONNECTIONS`	Lists the inactive database connections of the CDF	SH INACT CO, SHINACO

Database Groups

Group related database connections.

Command	Description	Aliases
`ADD GROUP`	Creates a Database Group, through an interactive wizard	ADD GR, ADDGR
`DEL GROUP`	Deletes a Database Group by making it inactive; refused while a connection uses it	DEL GR, DELGR
`EDIT GROUP`	Changes a Database Group, or reactivates it, through an interactive wizard	EDIT GR, EDGR
`SHOW ALL GROUPS`	Lists every Database Group, active or inactive	SHALGR
`SHOW GROUP`	Lists the connections of a connection's Database Group, with their Environments	SHOW ENVIRONMENTS, SH ENV, SHENV, SHOW ENVTS

Environments

Define DEV, QA, PROD and other environments.

Command	Description	Aliases
`ADD ENVIRONMENT`	Creates an Environment, such as DEV or PROD, through an interactive wizard	ADD ENV, ADDENV
`DEL ENVIRONMENT`	Deletes an Environment by making it inactive; refused while a connection uses it	DEL ENV, DELENV
`EDIT ENVIRONMENT`	Changes an Environment, its Production flag, or reactivates it, through an interactive wizard	EDIT ENV, EDENV
`ENV`	Switches to an Environment of the current Database Group	ENVT, CONNECT ENVIRONMENT
`SHOW ALL ENVIRONMENTS`	Lists every Environment of the CDF, active or inactive, with its Production flag	SHALENV, SHOW ALL ENVTS

Configure SQL automatically executed when a connection opens.

Command	Description	Aliases
`ADD LOGIN SCRIPT LINE`	Adds a SQL statement at the end of a connection's login script	ADD LOG SC LI, ADDLOGSCLI
`DEL LOGIN SCRIPT LINE`	Deletes a line of a connection's login script	DEL LOG SC LI, DELLOGSCLI
`EDIT LOGIN SCRIPT LINE`	Changes a line of a connection's login script, through an interactive wizard	EDIT LOG SC LI, EDLOGSCLI
`MOVE LOGIN SCRIPT LINE`	Moves a line of a connection's login script up or down by one position	MOVE LOG SC LI, MOVLOGSCLI
`SHOW LOGIN SCRIPT`	Shows the login script of a connection: the SQL statements run each time it opens	SH LOG SC, SHLOGSC

Running Queries

Execute SQL and work with query results.

Command	Description	Aliases
`ALL`	Shows every row of a table, as SELECT * FROM the table would
`CNT`	Counts the rows of a table, as SELECT COUNT(*) FROM the table would
`EDIT`	Opens the BroadSQL Editor on the Scripts Library, or on one of its Scripts	ED
`EXPAND`	Replaces the * of the last query with the real column names, without running it
`FORMAT`	Lays out the last query on several lines for readability, without changing its meaning or running it
`REPEAT`	Repeats a query, a block of queries or a script at a fixed interval, to monitor
`SHOW QUERY`	Shows the last SQL query of the session, the one that / runs again	SH QU, SHQU

Database Exploration

Explore schemas, tables, columns and database metadata.

Command	Description	Aliases
`COMPARE TABLE STRUCTURE`	Compares the columns of a table between the current connection and another connection	CM TA ST, CMTAST
`DESCR`	Describes the columns of a table: name, type, size, nullability and default value	DESC, DESCRIBE
`FIND COLUMN`	Finds the columns, and their tables, whose name contains the text you type, in the whole database	FI COL, FICOL, SHOW COLUMN, SH COL, SHCOL
`FIND FK`	Finds foreign keys whose name, tables or columns contain the input text	FIND REFERENCE, FIND REFERENCES, FI FK, FIFK
`FIND INDEX`	Finds indexes whose name, table or columns contain the input text	FIND INDEXES, FI IX, FIIX
`LINK TABLE`	Links a table of another connection into the current H2 database, without copying its rows	LINKT
`SHOW CATALOGS`	Lists the catalogs of the current database	SH CA, SHCA
`SHOW DBINFOS`	Shows the current connection, its JDBC driver and the database product and version	SH DBIN, SHDBIN
`SHOW DRIVERS`	Lists the JDBC drivers found in the drivers and lib folders	SH DR, SHDR
`SHOW FK`	Shows the foreign keys declared by a table and what they reference	SHOW FOREIGN KEYS, SH FK, SHFK
`SHOW INDEXES`	Shows the indexes of a table, their columns and whether they are unique	SHOW INDEX, SH IX, SHIX
`SHOW PK`	Shows the primary key columns of a table	SHOW PRIMARY KEYS, SH PK, SHPK
`SHOW REFERENCES`	Shows the foreign keys of other tables that reference a table	SHOW REFS, SH REF, SHREF
`SHOW SCHEMAS`	Lists the schemas of the current database	SH SC, SHSC
`SHOW TABLES`	Lists the tables, not the views, whose schema and name contain the text you type	SH TA, SHTA
`SHOW VIEWS`	Lists the views whose schema and name contain the text you type	SH VI, SHVI

Export & Local Data

Export and preserve data using Excel, ODS, CSV, H2 and more.

Command	Description	Aliases
`COPY RESULT`	Copies the last result, of a SQL query or a RUN, to the clipboard, ready to paste into a spreadsheet
`DUMP`	Exports a table, a query result or a Script result to a file or to a table of a local H2 database
`PULL`	Compatibility alias of DUMP: exports a query's results to a file or an H2 database

Data Import

Import data into existing database tables.

Command	Description	Aliases
`LOAD`	Loads the rows of a CSV file into a table, with safe INSERT statements, all or nothing	LO

Scripts Library

Run and manage reusable Scripts: text files of SQL and BroadSQL commands.

Command	Description	Aliases
`@`	Runs a Script: a text file of SQL statements and BroadSQL commands
`ECHO`	Prints a single-quoted message, with ${name} variable values
`LET`	Assigns a SQL scripting variable from a literal, another variable, or a one-row query
`LIB DEL`	Deletes a Script of the Scripts Library by archiving it, after confirmation; it can be restored	LI DE, LIDE
`LIB EDIT`	Opens a Script of the Scripts Library in the BroadSQL Editor, or offers to create it	LI ED, LIED
`LIB FIND`	Finds the Scripts of the Scripts Library whose content contains a text, ignoring case	LI FI, LIFI
`LIB LINT`	Checks the Scripts of the Scripts Library for consistency problems, without changing them	LI LN, LILN
`LIB LIST`	Lists the Scripts of the Scripts Library that apply to the current connection, or all of them, or the archived ones	LI LI, LILI
`LIB RESTORE`	Restores an archived Script of the Scripts Library to its place	LI RS, LIRS
`LIB RUN`	Runs a Script from the Scripts Library	LI RU, LIRU
`LIB SHOW`	Shows the content and the declared parameters of a Script of the Scripts Library	LI SH, LISH
`LIB TREE`	Shows the folders and Scripts of the Scripts Library as a tree	LI TR, LITR
`LIB UNDO`	Restores the most recently archived Script of the Scripts Library	LI UN, LIUN
`ON ERROR`	In a Script, stops at the first failed statement (STOP) or continues (CONTINUE, the default)
`OUTPUT`	In a Script, hides routine output (QUIET) or shows it again (NORMAL, the default)
`SHOW SCRIPT VARIABLES`	Lists the SQL scripting variables of the session (set with LET or as Script arguments)

Light Scripting (JS)

Run and manage JavaScript against query results.

Command	Description	Aliases
`JS EVAL`	Runs a short piece of JavaScript typed on the command line	JS EV, JSEV
`JS FIND`	Finds the JavaScript scripts whose content contains a text, ignoring case	JS FI, JSFI
`JS LIST`	Lists the JavaScript scripts that apply to the current connection, or all of them	JS LI, JSLI
`JS RUN`	Runs a saved JavaScript script of the JsScripts folder, with optional arguments	JS RU, JSRU

Session & Settings

Control the BroadSQL session and display behavior.

Command	Description	Aliases
`SET AUTOCOMMIT`	Turns autocommit on or off for the current connection	SE AU
`SET LIST`	Shows query results as a table (OFF, the default) or one row at a time as a form (ON)	SELI
`SET MASTER PASSWORD`	Changes the master password that protects the Connections Definition File (CDF)	SET MAPA
`SET PASSWORD`	Changes the password of the current connection's account, in the database (H2, Derby, HSQLDB) and in the CDF	SE PA
`SET SCHEMA`	Changes the current schema of the connection	SE SC, SESC
`SHOW AUTOCOMMIT`	Shows whether autocommit is on or off for the current connection	AUTOCOMMIT, SH AU, SHAU

API Client

Configure, import, browse and execute HTTP API endpoints with the Universal API Client.

Command	Description	Aliases
`CONFIG API`	Opens the window that configures APIs, environments, authentication, folders and endpoints (Windows only)
`IMPORT API BRUNO`	Imports a Bruno API collection (single-file OpenCollection YAML) into the API catalog, or imports it again
`RUN`	Calls an endpoint of the connected API by its URL, GET by default
`SHOW ALL APIS`	Lists every API of the API catalog: ID, name, type and description	SHALAP, ALL APIS
`SHOW API ENVIRONMENTS`	Lists the environments of one API: ID, name and base URL	SHAPENV, ALL ENVT, ALL ENV
`SHOW API ENVIRONMENT`	Shows one environment of the connected API in detail: ID, name, base URL and variables	SHAPIENV
`SHOW ENDPOINTS`	Lists the endpoints of the connected API, or of a given API, filtered by method or text	ENDPOINTS, ALL ENDPOINTS, SHENDS
`SHOW ENDPOINT`	Shows one endpoint in detail: URL, parameters, headers, body and authentication type	ENDPOINT, SHEND
`SYNTAX`	Shows how to call an endpoint of the connected API: its URL, its parameters and RUN examples
`VAR`	Sets an API variable for the session, used by RUN for a :name placeholder, or saves it with PERSIST

General

Basic session tools: HELP, VERSION, CONFIG, CLS, PRINT.

Command	Description	Aliases
`CLS`	Clears the screen	CLEAR
`CONFIG`	Opens the window that manages connections, Database Groups, Environments, login scripts and application settings (Windows only)
`HELP`	Lists the command categories, or shows the help of a category, a command or a topic
`PRINT`	Prints text on the console; in Scripts, prefer ECHO	PRNT
`SHOW EXTENSION ERRORS`	Lists the extension and command loading failures recorded at startup
`VERSION`	Displays the current version of BroadSQL	VERS

;	Ends every command, BroadSQL's own or SQL. A command may span several lines; it runs at its closing `;`. A `;` inside a quoted value is text.
/	Runs the last SQL statement again, on the current connection. Typed on a line by itself.
/ <environment>	Runs the last SQL statement once on the connection of another Environment of the same Database Group, without switching.
//	Shows the last query on one line without running it, like `SHOW QUERY`.
@<script> [name=value ...]	Runs a Script: a plain name is in the Scripts Library, `./` is next to the current Script or the working directory, an absolute path is anywhere.
${name}	A Script variable or argument, sent to the database as a typed value, never pasted into the SQL text.
<@file>	Expands to the values of a text file, one per line, quoted and separated by commas.
<@csv:<path>:<column>>	Expands to the values of one named column of a CSV file.
<@excel:<path>:<sheet>:<column>>	Expands to the values of one named column of one sheet of an `.xlsx` workbook.
<@last:<column>>	Expands to the values of one column of the result you just displayed, so one query feeds the next.
<@clipboard>	Expands to the values on the clipboard, one per line: a column copied from a spreadsheet or a ticket.
-- and /* */	Comments, ignored in Scripts. A `--` line at the top of a Script may hold a directive.
exit	Ends BroadSQL and closes every connection cleanly. Lowercase, without `;`.
:name	In a `RUN` URL, a runtime placeholder resolved for that call: a session `VAR` first, then the API environment, then the endpoint's saved value or default.
${ENV:NAME}	In an API definition, the value of an operating system environment variable, for example a token.
-- @param <name> <type> [= <default>] ["<description>"]	Declares one typed parameter: `TEXT`, `INTEGER`, `DECIMAL`, `BOOLEAN`, `DATE`, `TIMESTAMP` or `LIST OF <type>`. With a default it is optional.
-- @params: <name>, ...	Lists the arguments every call must pass, without types. Prefer `-- @param` for a new Script.
-- @description: <text>	A one-line description, shown by search and in the Editor.
-- @instance: <group>[, ...] \| ALL	The Database Group(s) the Script is about. A mismatch with the current connection prints a warning; nothing is blocked.
-- @environment: <environment>[, ...] \| ALL	The Environment(s) the Script applies to. A mismatch prints a warning; nothing is blocked.
-- @tags: <tag>, ...	Free text tags, matched by `LIB FIND` and grouped by `LIB LIST ... BY TAG`.
-- @status: draft \| stable \| deprecated	Information only, shown in listings.
-- @access: read \| write	Declares whether the Script changes data. Never enforced; `LIB LINT` reports a `read` Script that may write.
-- @dialect: <type>[, ...] \| ANY	The connection types the Script is written for. On another type, BroadSQL warns and runs it anyway.

Patterns	Use files, clipboard, spreadsheets and previous query results in SQL, not commands of their own, so not listed in a category below.
Extending BroadSQL	Add your own commands from a JAR file. BroadSQL does not currently ship with any extension commands, so none is listed above.

Found something wrong or unclear on this page? Tell us, with the page address.