SQLite: show tables, columns and CREATE statements

By Anil Gautam · Published and reviewed September 20, 2026

In a SQLite GUI, query sqlite_schema to list tables. In the SQLite terminal shell, you can also use .tables. If .tables fails in your SQL editor, try the SQL query below instead.

List tables and views

SELECT type, name, tbl_name
FROM sqlite_schema
WHERE type IN ('table', 'view')
  AND name NOT LIKE 'sqlite_%'
ORDER BY type, name;

The filter hides SQLite's internal objects to keep the initial list focused on application data. Remove that filter when you need to inspect SQLite’s internal objects. These are the objects in the main database; attached databases have their own schema tables. See the SQLite schema table reference.

Check which file you actually opened

PRAGMA database_list;

Compare the returned file path with the application's configured database path. A working connection to an empty file can look like a missing-data problem. Some SQLite tools create a new file when given a nonexistent path, so verify the path before concluding the database was wiped.

Inspect columns and table definition

For an illustrative table named orders:

PRAGMA table_xinfo('orders');
SELECT sql FROM sqlite_schema
WHERE type = 'table' AND name = 'orders';

table_xinfo includes information about generated and hidden columns beyond the simpler table_info view. Read the pragma reference when interpreting its fields. The CREATE statement is useful for seeing declared constraints, but it does not establish that all existing data satisfies every application rule.

Inspect rows without changing the source

Start with a small SELECT using actual column names and an explicit order. If the database belongs to another running application, obtain a consistent SQLite-aware snapshot before experimenting. A database file accompanied by WAL state needs more care than a standalone file copied at an arbitrary moment.

In plainDB, open the SQLite connection, compare the schema tree with the SQL result, and use the structure view to inspect the selected table. Refresh after a schema change. Keep the original file untouched while investigating unfamiliar application data, and record the path with any screenshots so your findings can be reproduced.

Continue reading

Open a SQLite database · Understand WAL files · SQLite viewer

For the desktop workflow, compare plainDB features on Mac and Windows or download the 14-day trial.