SQLite Show Schema: A Comprehensive Guide

Understanding the schema of your SQLite database is crucial for efficiently querying and manipulating data. SQLite provides several useful commands to allow you to introspect your database schema. In this guide, we’ll explore these commands through examples to better understand how to view schema information in SQLite.

A Quick Intro to SQLite Schemas

First, let’s do a quick review of some SQLite schema basics. A schema defines the structure and layout of a SQLite database. It specifies things like:

• The tables in the database
• The columns in each table
• The data types for each column
• Any constraints on the columns (e.g. primary keys, foreign keys, etc.)

Schemas allow us to enforce structure on our data. For example, we may define a users table with columns for id, name, email, etc. When inserting data, we can rely on this predefined structure.

Now let’s look at how to introspect this schema in a SQLite database using some handy schema commands.

.schema – View CREATE Statements

The .schema command allows you to view the CREATE statements SQLite used to initialize all tables and indices in your database.

For example, given a users table:

CREATE TABLE users (
  id INTEGER PRIMARY KEY, 
  name TEXT,
  email TEXT
);

Running .schema would show us:

.schema

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  name TEXT,
  email TEXT
);

This provides useful insight into the foundational schema of the database.

.tables – List All Tables

To simply get a list of tables in your database, use the .tables command:

.tables

users
posts
comments

This provides a quick overview of the main data tables.

PRAGMA table_info(<table>); – View Table Schema

To get more detailed schema information for a specific table, use PRAGMA table_info(<table>);.

For example:

PRAGMA table_info(users);

id|name|type|notnull|default|pk  
--|----|----|-------|-------|---
1|id|INTEGER|1||1
2|name|TEXT|0||0
3|email|TEXT|0||0

This shows the column names, data types, not null constraints, default values, and primary key designations for the users table.

PRAGMA index_list(<table>) – List Indices on Table

To view indices on a table, use PRAGMA index_list(<table>):

PRAGMA index_list(users);

0|users_email_index|users|email
1|sqlite_autoindex_users_1|users|id

This lists the index name, table, and column(s) indexed.

Putting it All Together

With these SQLite schema commands, we can comprehensively introspect database schema information:

• .schema provides the big picture on all table definitions
• .tables lists all tables
• PRAGMA table_info() gives column details for a specific table
• PRAGMA index_list() shows indices on a table

For example, we could view schema information for an analytics database as follows:

.schema 

/* Displays CREATE statements for:
   - users
   - events 
   - tracking data
   - etc.
*/

.tables

/*
users
events
tracking_data
*/

PRAGMA table_info(events);

/*
Shows column schema for events table
*/

PRAGMA index_list(tracking_data); 

/* 
Shows indexes defined on tracking_data table
*/

Hopefully this guide provides a comprehensive overview of how to introspect and understand the schema of your SQLite databases using some simple but powerful schema commands! Proper schema design and understanding will lead to more efficient database usage down the line.