Dot Commands

The dot commands for sqlite can be used to change the output format, fetch information about the database, and modify some settings. In this appendix, each dot command is shown first followed by an explanation.

Obtaining a List of Dot Commands

 .help 

Displays the full list of dot commands for the installed sqlite with brief descriptions.

Changing the Output Format

 .mode list 

The default output mode. Displays one line per record, with each column separated by a specific character or string. The default separator is the pipe character (|).

 .separator string 

Changes the separator for list mode output to string.

 .mode lines 

Causes sqlite to output each column in the result of a query to be displayed on a line by itself, with the value prefixed by the name of the column. Subsequent records are separated by a blank line.

 .mode columns 

Displays one line per record with data aligned in fixed-width columns.

 .width width1 width2 ... widthN 

Specifies the width in characters for each column in turn, where width1 is the leftmost column returned by the query.

A width setting of 0 (the default) will automatically size the column to whichever is largest of: the length of the column heading, the length of the value in the first row of data, or 10 characters.

 .headers on|off 

Determines whether column headings are displayed in column output mode.

 .mode insert table-name 

Causes a list of full INSERT statements to be generated for the records returned by the query. The table-name argument determines the name of the table for the INSERT statements.

 .mode html 

The output is generated as an XHTML table with one set of <TR> tags and each column as a <TD> element. If .headers on has been specified, <TH> tags are used for the column headings.

 .nullvalue string 

Specifies a value to display wherever a NULL value appears in the result of the query.

 .echo on|off 

When echo is set to on, the SQL command being executed is included as the first line of the output.

 .explain on|off 

Specifies a fixed-width column output mode suitable for the result of the EXPLAIN SQL command. Using .explain off reverts the output settings to their former values.

 .show 

Displays the current settings for echo, explain, headers, mode, nullvalue, output, separator, and width.

Reading SQL Commands from a File

 .read filename 

Executes one or more SQL commands from filename in turn. A syntax error in the file will cause an error to be displayed but will not prevent execution of the remaining commands.

Sending Output to a File

 .output filename 

Specifies that the output from queries should be sent to filename. If filename already exists, it is overwritten with the new output.

 .output stdout 

Specifies that output should be sent to standard outputusually the screen, but sqlite may also be used in a shell script with output redirection.

Getting Information about the Database Schema

 .databases 

Lists the names of currently attached databases and the paths to the database files.

 .tables pattern 

Lists all tables in the primary attached database that contain pattern. If no pattern is given, all tables in the database are listed. Note that tables in databases attached with the ATTACH DATABASE command are not listed.

 .schema table-name 

Shows the CREATE TABLE statement that was used to create table-name. If no table-name is given, the entire database schema is displayed.

 .dump table-name 

Generates a list of SQL commands that contain the CREATE TABLE and INSERT statements required to re-create the table specified. If no table-name is given, the entire database is dumped.

 .indices table-name 

Lists the names of each index in turn on the given table. The table-name argument is required.

Changing the sqlite Prompts

 .prompt main continue 

Allows you to customize the prompts displayed when using sqlite. The two arguments replace the main prompt (usually sqlite>) and the continuation prompt (usually ...>) respectively. If only one argument is given, it is assumed to be the main prompt.

Setting the Database Timeout

 .timeout ms 

Specifies the amount of time in milliseconds that sqlite will wait for a filesystem lock to clear when attempting to open a database file. If the time is exceeded, an error message is returned. The default value is zero, instructing sqlite to always return an error immediately.

Exiting the sqlite Tool

 .quit .exit 

Either of these commands closes the running sqlite program. The program will also exit if an end-of-file character is received.