GUIDES / SQL RECIPES

SQLite Show Tables: List Tables, Views and Columns

SQLite stores schema definitions in sqlite_schema. These SELECT queries work in the browser workspace as well as ordinary SQLite clients. The .tables command belongs to the SQLite command-line shell; it is not SQL.

List user tables

Filter by type and omit names reserved for SQLite. The sample returns customers, order_items, orders and products in alphabetical order.

SELECT name FROM sqlite_schema
WHERE type = 'table' AND name NOT LIKE 'sqlite_%'
ORDER BY name;

Open this example for review →

Include views and CREATE statements

Views are saved queries rather than separate stored tables. The sample includes customer_orders. Inspect the SQL text before copying a definition into another database.

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

Open this example for review →

Inspect columns

table_info shows column names, declared types, defaults and primary key positions. Use table_xinfo when you also need generated or hidden columns. The workspace Schema tab provides this information without a query.

PRAGMA table_info("customers");

Open this example for review →

Try it in the workspace →

Keep exploring

SQLite syntax reference · Practice with a learning path · Sample datasets

How to Open a .db File in Your Browser

Reference: Official SQLite documentation