Segfault > IT > Lookups > SQLite3 commands
  • Create or open a database:
    sqlite3 [database_file]
  • List all tables:
    .tab
  • Show table structure:
    pragma table_info([tablename]);
  • Show column headers:
    .headers on
  • Other special commands:
    sqlite> .help
    • .backup ?DB? FILE      Backup DB (default "main") to FILE
    • .bail ON|OFF           Stop after hitting an error.  Default OFF
    • .databases             List names and files of attached databases
    • .dump ?TABLE? ...      Dump the database in an SQL text format
    • .echo ON|OFF           Turn command echo on or off
    • .exit                  Exit this program
    • .explain ON|OFF        Turn output mode suitable for EXPLAIN on or off.
    • .genfkey ?OPTIONS?     Options are:
      --no-drop: Do not drop old fkey triggers.
      --ignore-errors: Ignore tables with fkey errors
      --exec: Execute generated SQL immediately
      See file tool/genfkey.README in the source distribution for further information.
    • .header(s) ON|OFF      Turn display of headers on or off
    • .help                  Show this message
    • .import FILE TABLE     Import data from FILE into TABLE
    • .indices TABLE         Show names of all indices on TABLE
    • .iotrace FILE          Enable I/O diagnostic logging to FILE
    • .load FILE ?ENTRY?     Load an extension library
    • .mode MODE ?TABLE?     Set output mode where MODE is one of:
      csv      Comma-separated values
      column   Left-aligned columns.  (See .width)
      html     HTML <table> code
      insert   SQL insert statements for TABLE
      line     One value per line
      list     Values delimited by .separator string
      tabs     Tab-separated values
      tcl      TCL list elements
    • .nullvalue STRING      Print STRING in place of NULL values
    • .output FILENAME       Send output to FILENAME
    • .output stdout         Send output to the screen
    • .prompt MAIN CONTINUE  Replace the standard prompts
    • .quit                  Exit this program
    • .read FILENAME         Execute SQL in FILENAME
    • .restore ?DB? FILE     Restore content of DB (default "main") from FILE
    • .schema ?TABLE?        Show the CREATE statements
    • .separator STRING      Change separator used by output mode and .import
    • .show                  Show the current values for various settings
    • .tables ?PATTERN?      List names of tables matching a LIKE pattern
    • .timeout MS            Try opening locked tables for MS milliseconds
    • .timer ON|OFF          Turn the CPU timer measurement on or off
    • .width NUM NUM ...     Set column widths for "column" mode