How to Use sqlite3.exe to Explore and Query a SQLite Database

SQLite is one of the easiest databases to work with because it stores everything in a single file and does not need a server running in the background. If you have a .db, .sqlite, or similar database file, the sqlite3.exe command-line tool lets you inspect it, list its tables, and run SQL queries directly from your terminal.

This guide walks through the basics of using sqlite3.exe, including how to open a database, see what tables it contains, inspect table structure, and query data.

What is sqlite3.exe?

sqlite3.exe is the command-line shell for SQLite on Windows. It lets you:

• open a SQLite database file
• list tables
• inspect schema and columns
• run SQL queries
• export results
• make changes to the database

It is very handy when you want to quickly inspect an app database, troubleshoot data, or test SQL statements.

Getting sqlite3.exe

If you do not already have it, download the SQLite command-line tools from the official SQLite website. You want the Windows command-line tools package, which includes sqlite3.exe.

After extracting it, you can either:

• run it from that folder directly, or
• add the folder to your system PATH so you can call sqlite3 from anywhere

For example, if it is in:

C:\sqlite\sqlite3.exe

you can run it directly from Command Prompt or PowerShell.

Opening a database

To open a database file, use:

sqlite3.exe mydatabase.db

Or with a full path:

sqlite3.exe "C:\path\to\mydatabase.db"

Once opened, you will enter the SQLite shell and see a prompt like:

sqlite>

From there, you can type SQLite shell commands and SQL statements.

Checking what tables the database has

One of the first things you usually want to know is what tables exist.

Use:

.tables

This will list all tables in the database.

For example:

sqlite> .tables
customers  orders  products

That gives you a quick overview of the database contents.

Viewing the database schema

If you want to see the full schema for the database, use:

.schema

This prints the SQL used to create all tables, indexes, and other objects.

If you only want the schema for one table, specify its name:

.schema customers

This is useful when you want to see column names, data types, primary keys, and constraints.

Example output:

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT,
    created_at TEXT
);

Checking the columns in a table

A neat way to inspect a table’s structure is:

PRAGMA table_info(customers);

This returns one row per column, showing details like:

• column id
• column name
• data type
• whether it can be null
• default value
• whether it is part of the primary key

Example:

sqlite> PRAGMA table_info(customers);
0|id|INTEGER|0||1
1|name|TEXT|1||0
2|email|TEXT|0||0
3|created_at|TEXT|0||0

If you want clearer output, turn headers on first:

.headers on
.mode column
PRAGMA table_info(customers);

Querying data from a table

To view data, use a normal SQL SELECT query.

Example:

SELECT * FROM customers;

If the table is large, limit the output:

SELECT * FROM customers LIMIT 10;

You can also query specific columns:

SELECT id, name, email FROM customers LIMIT 10;

Or filter results:

SELECT * FROM customers WHERE name = 'John';

Or sort them:

SELECT * FROM customers ORDER BY created_at DESC LIMIT 10;

Making query results easier to read

The default SQLite output is compact, but you can improve readability.

Turn on headers:

.headers on

Set column mode:

.mode column

Now your results will look more like a table.

Example session:

sqlite> .headers on
sqlite> .mode column
sqlite> SELECT id, name, email FROM customers LIMIT 5;
id  name         email
--  -----------  ---------------------
1   Alice Jones  alice@example.com
2   Bob Smith    bob@example.com
3   Jane Doe     jane@example.com

Other useful modes include:

.mode csv
.mode line
.mode list

csv is useful if you want output that can be copied into a spreadsheet.

Finding all objects in the database

Besides tables, SQLite databases can also contain indexes, views, and triggers.

To see everything in the schema table, run:

SELECT name, type FROM sqlite_master ORDER BY type, name;

This shows the names and types of objects stored in the database.

Example:

SELECT name, type FROM sqlite_master ORDER BY type, name;

Possible output:

customers|table
orders|table
products|table
idx_orders_customer_id|index

If you only want tables:

SELECT name FROM sqlite_master WHERE type='table' ORDER BY name;

This is especially handy in scripts.

Checking which database is open

To show the current database connection, use:

.databases

Example:

sqlite> .databases
main: C:\path\to\mydatabase.db

That helps confirm you are working in the right file.

Exiting the SQLite shell

When you are done, type:

.quit

You can also use:

.exit

Running a query directly from Command Prompt

You do not always need to enter the interactive shell. You can run a query directly from the command line.

Example:

sqlite3.exe mydatabase.db "SELECT name FROM sqlite_master WHERE type='table';"

That is useful for scripting or quick checks.

You can also combine formatting commands:

sqlite3.exe mydatabase.db ".headers on" ".mode column" "SELECT * FROM customers LIMIT 5;"

Exporting data to CSV

If you want to export query results to a CSV file:

.headers on
.mode csv
.output customers.csv
SELECT * FROM customers;
.output stdout

This writes the results to customers.csv.

After that, .output stdout sends output back to the screen.

Common commands you will probably use most

Here are the ones you will reach for often:

.tables
.schema
.schema table_name
.databases
.headers on
.mode column
.quit

And common SQL:

SELECT * FROM table_name LIMIT 10;
PRAGMA table_info(table_name);
SELECT name FROM sqlite_master WHERE type='table';

Example workflow

Here is a simple real-world workflow for inspecting a database:

Open the database:

sqlite3.exe appdata.db

Check what tables exist:

.tables

Inspect one table:

.schema users

Or:

PRAGMA table_info(users);

Make the output easier to read:

.headers on
.mode column

Query some data:

SELECT * FROM users LIMIT 10;

Exit:

.quit

Final thoughts

sqlite3.exe is a lightweight but powerful tool. If all you need is to inspect a database, see what tables it has, and run a few queries, it is often faster than opening a GUI database browser.

Once you know a few key commands like .tables, .schema, PRAGMA table_info(...), and SELECT ..., you can quickly explore almost any SQLite database file.

If you are troubleshooting an application, reverse-engineering a local data file, or just learning SQL, sqlite3.exe is a great place to start.