BroadSQLOn this page
BroadSQL cheat sheet
The commands you type most, the shortcuts and the keys, on one page. It prints on two sheets; every name links to its full reference.
First ten commands
CONNECT <id>; | Open a connection |
SHOW TABLES; | List the tables |
DESCR <table>; | Its columns and types |
CNT <table>; | Count its rows |
SELECT ...; | Any SQL, sent as is |
/ | Run the last query again |
DUMP / TO f AS XLSX; | Export what is on screen |
HELP FIND <text>; | Search the commands |
DISCONNECT; | Close the connection |
exit | Quit (lowercase, no ;) |
Connections and environments
CONNECT <id> CON | Open a connection |
ENV <environment> | Same application, other Environment |
/ PROD | Last query once on PROD, stay here |
SHOW CONNECTION SHCO | Where am I? |
SHOW GROUP SHENV | The Environments of my application |
SHOW ALL CONNECTIONS SHALCO | Every connection |
PING <id> CHECK | Test without opening |
DISCONNECT BYE | Close the connection |
LOGOUT | Close everything, ask the master password |
Exploring
SHOW TABLES [CUST%] SHTA | Tables, filtered by name |
SHOW VIEWS SHVI | Views |
DESCR <table> [ALPHA] DESC | Columns, in table or name order |
SHOW PK <table> SHPK | Primary key |
SHOW FK <table> SHFK | What it references |
SHOW REFERENCES <table> SHREF | What references it |
FIND COLUMN <text> FICOL | Where is that column? |
COMPARE TABLE STRUCTURE <t> WITH <id> | Same table, another connection |
ALL <table> / CNT <table> | Every row / the row count |
Running queries again
/ | Run the last query again |
// or SHOW QUERY | Show it without running it |
REPEAT EVERY 30s; | Watch it until Ctrl+C |
EXPAND | Replace * by the column names |
FORMAT | Lay out the last query |
SET LIST ON | One row at a time, as a form |
Lists of values
IN <@clipboard> | Values copied, one per line |
IN <@last:ID> | A column of the last result |
IN <@ids.txt> | A text file, one per line |
IN <@csv:f.csv:ID> | A named CSV column |
IN <@excel:f.xlsx:Sheet:ID> | A named Excel column |
Export and load
DUMP <table> TO f AS CSV | A whole table |
DUMP (<query>) TO f.TAB AS XLSX | A query, into one worksheet |
DUMP / TO f AS MD | What is on screen |
... MODE APPEND | Add rows instead of replacing |
DUMP t TO COPY.t AS H2 MODE MERGE KEY(ID) | Keep a local copy fresh |
COPY RESULT | Last result to the clipboard |
LOAD <table> f.csv PREVIEW LO | Check a CSV, then without PREVIEW |
Formats: CSV, TEXT, JSON, XLSX, ODS, MD, HTML, H2.
Scripts
@dir/s.sql name=value | Run a Script of the library |
LIB LIST [ALL] LILI | List the Scripts |
LIB FIND <text> LIFI | Search their content |
LIB SHOW <script> LISH | Content and parameters |
LIB LINT LILN | Check the library |
LET x = 42; | A variable, used as ${x} |
ON ERROR STOP; | Stop at the first failure |
-- @param id INTEGER = 1 | A typed parameter |
EDIT ED | Open the BroadSQL Editor |
Console keys
| Tab | Complete; again for the next candidate |
| Up Down | Earlier commands, across sessions |
| Ctrl+R | Search the history |
| Esc | Abandon what you are typing |
| Ctrl+C | Stop the running command |
| Ctrl+L | Clear the screen |
| Ctrl+U / Ctrl+K | Delete to the start / the end of the line |
HELP SHORTCUTS; lists every key in the console.
Settings to know
DefaultFolder | Where DUMP writes |
CsvSeparator | SEMICOLON (default) or COMMA |
MaxRowsOnScreen | Rows shown on screen (0: no limit) |
RememberLastConnection | Reopen the last connection at startup |
ProductionConfirmEveryChange | Ask before every change, or once per session |
IsLogActivated | Keep the activity log |
In conf/BroadSQL.ini (Windows) or conf/broadsqlux.ini (Linux), or in CONFIG: see Application settings.
Found something wrong or unclear on this page? Tell us, with the page address.