Search an SQL database using sqlite3

https://www.sqlite.org/index.html

sqlite implements a small, fast, self-contained, high-reliability, full-featured, SQL database engine to query .sql files.

Getting started

To use sqlite, you must have a valid metadata file with a .db file extension, and an installed version of SQL. This tutorial assumes knowledge of the command line.

To start the sql engine, type:

sqlite3

The prompt will change, showing that the sqlite engine has been initialised. To open a metadata file, type:

.open filename.db

Displaying tables and columns

You can then display tables with:

.tables

Based on what is returned, you can then query the content of each table with commands such as:

SELECT * FROM mytable;

You may want to have a more human-readable view of the data that is printed in the terminal, by organising the headings and columns:

.headers ON
.mode column

You will get output like:

.headers on
.mode column
select * from mytable;
id          foo         bar
----------  ----------  ----------
1           val1        val2
2           val3        val4

Getting a list of column names

An alternative way to get a list of column names not mentioned here that is cross platform and does not rely on the sqlite3.exe shell is to select from the PRAGMA_TABLE_INFO() table value function.

SELECT name FROM PRAGMA_TABLE_INFO('your_table');
name      
tbl_name  
rootpage  
sql

You can check if a certain column exists by querying:

SELECT 1 FROM PRAGMA_TABLE_INFO('your_table') WHERE name='column1';
1