Skip to content

Tips_&_Tricks

Paweł Salawa edited this page May 14, 2021 · 2 revisions

Deleting row vs deleting contents of selected cells

When working in a grid data view (viewing table data, or query results), you can use "Delete" keyboard key to delete all rows included in current selection, or you can use "Backspace" key to clear the contents of selected cells (i.e. set their values to NULL).

Group your databases

You can group your databases by creating groups (virtual directories) in the list of databases (right-click on the list - group management entries are in the menu). You can drag&drop databases between groups. When group is being delete, all databases from that group are moved to the parent group, or if there is not parent group, they are moved to the top level of the list.

Change order of columns in table with drag&drop

You can change order of columns in the table by opening Table Window (just double click on a table) and using drag&drop (in Structure tab)

A more classic alternative is to select a column and press Move column up or Move column down buttons from toolbar (blue icons with up/down arrows).

Move or copy tables across databases

You can easly move or copy tables (and views) across all databases, even between SQLite 2 and 3 versions. Just drag&drop them. If SQLiteStudio detects problem, that makes it impossible to copy/move object, it will let you know what is the problem. SQLiteStudio will also inform you about any minor changes that are needed to copy some objects from SQLite 3 to SQLite 2 and vice versa (for example SQLite 2 doesn't support AUTOINCREMENT statement). SQLiteStudio will detect such issues for you and will automatically fix them, just letting you know about it.

The same can be accompished with Copy and Paste context menu entries (right click on selected table/view), although you cannot move objects using this method, only copy them. This is to prevent from accidental deleting objects from the source database.

Asking for SQL completion

When typing query in SQL Editor the editor can assist you with the SQL syntax and also with SQLite database names (tables, columns, etc). This happens automatically when you type for example table name and a dot, like this:

SELECT * FROM tab WHERE tab.

. You can always trigger the assistant manually by pressing "Ctrl+Space" key combination. This is the default shortcut, but it can be changed in configuration dialog.

ROWID of data row quickly

When browsing table data (or results from SQL query) hold mouse over some specific cell and the "hint" will appear with all information that SQLiteStudio knows about this cell. That includes its column details and the ROWID.

If there's no ROWID in the "hint", it means that SQLiteStudio was unable to extract ROWID for this row. That is the case for example in SQL query results, when the specific cell is result of some expression and not a direct result of table data.

View/Edit data as image (or other supported format), not as plain text

By default all data in cells is presented as text, but sometimes data represents images, or XML contents. In that case you might want to see the actual image, or have the XML highlighted. To have that, you can either switch to the Form View tab, or you can right-click on the cell and select "Open in editor".

Execute current query, execute selected code, execute entire SQL editor contents

By default, SQL Editor window executes only the query that the cursor is placed in. You can change that behaviour in configuration dialog - you can choose to always execute entire SQL editor contents.

Regardless of the option above, you can always select a piece of SQL code you're interested in and press "Execute" and only the selected code will be executed.

Setting cell to an empty string, a NULL or deleting a row using keyboard

  • When a data cell is currently selected and "backspace" key is pressed, the cell value is set to NULL.
  • When a data cell is currently selected and "delete" key is pressed, the entire data row is marked for deletion (but not yet deleted, the deletion needs to be commited).
  • When a data cell is currently selected, "enter" key is presssed to enter editing mode and contents of cell is deleted, then cell value is set to en empty string.

Opening table mentioned in SQL query

When having a SQL query typed in SQL Editor window and it has some table names in it, hold "Ctrl" key and you will notice those table names become underlined. You can click them while holding "Ctrl" to open them, just like you would double-click on the same table in the databases list on the left side.

Also when you right-click on such table (or index, or trigger, or view) in the SQL Editor, you will get the same context menu as you would right-click on that table in the databases list.

Workarounds for stored procedures in SQLite

Custom SQL Functions

Code snippets

Clone this wiki locally