SQLite Show Tables

SQLite show tables makes it easy to get an overview of your database layout and dig into each table’s details. This comprehensive guide will explore the various techniques for viewing table schemas in SQLite databases.

Also read: SQLite Create Table

SQLite Show Tables Commands

The main commands for showing tables and schemas in SQLite are:

Let’s look at each of these in more depth, along with examples.

List All Tables using SQLite Show Tables .tables

The most straightforward to use SQLite Show Tables to display tables from a database is using the .tables command with no arguments:

.tables

This will output a list of all table and view names in the current database:

users
posts
comments
user_profiles
post_tags

By default .tables shows both tables and views. To only show tables, you can use .tables sqlite_master which will filter system views.

You can also filter the list only to show tables matching a specified pattern using .tables PATTERN:

.tables post%

posts 
post_tags

This example would match all tables starting with “post”. The % wildcard allows matching any number of trailing characters.

View CREATE Statement with SQLite Show Tables .schema

While .tables shows the names of tables. To see the schema of a table, you need to use the .schema command. This will display the CREATE TABLE statement used to generate the table:

.schema posts

CREATE TABLE posts (
  id INTEGER PRIMARY KEY,
  user_id INTEGER NOT NULL,
  title TEXT,
  body TEXT,
  publish_date DATE NOT NULL
);

This provides useful insight into the columns, data types, constraints, and indexes that make up a table.

You can specify any table name after .schema to view its definition.

Get Column Details with PRAGMA table_info()

For even more detailed information on a table’s columns, you can query the sqlite_master database using PRAGMA table_info().

This will return rows containing the column name, data type, whether NULL is allowed, and other properties of each column in a table:

PRAGMA table_info(posts);

cid|name|type|notnull|dflt_value|pk
0|id|INTEGER|1||1
1|user_id|INTEGER|1||0
2|title|TEXT|0||0
3|body|TEXT|0||0  
4|publish_date|DATE|1||0

Some key pieces of info here:

• cid – Column index starting at 0
• name – Column name
• type – Data type (TEXT, INTEGER, DATE, etc)
• notnull – 1 if column has a NOT NULL constraint
• pk – 1 if column is part of the primary key

This provides lower-level details that .schema does not show directly.

Open a Connection to an SQLite Database

To use these SQLite show table commands, you first need to open a connection to a database using the sqlite3 command line program:

$ sqlite3 database.db
SQLite version 3.7.15.2 2014-08-15 11:53:05
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>

This will start the interactive shell and allow you to enter commands.

Alternatively, you can execute SQL statements directly using sqlite3:

$ sqlite3 database.db ".tables" 
posts
users
comments

This opens the database, runs the .tables command, and exits.

Also read: SQLite Commands – A Comprehensive Guide

Examples of SQLite Show Tables

Let’s look at examples of using these SQLite Show Tables commands in a sample database.

First, create a simple database with two tables using SQLite statements:

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

CREATE TABLE posts (
  id INTEGER PRIMARY KEY,
  user_id INTEGER NOT NULL,
  title TEXT NOT NULL, 
  body TEXT NOT NULL
);

Now connect to this database and use .tables to view the tables:

sqlite> .tables
users  
posts

Check the schema for the posts table:

sqlite> .schema posts

CREATE TABLE posts (
  id INTEGER PRIMARY KEY,
  user_id INTEGER NOT NULL,
  title TEXT NOT NULL,
  body TEXT NOT NULL
);

And get column details using PRAGMA:

sqlite> PRAGMA table_info(posts);
0|id|INTEGER|1||1
1|user_id|INTEGER|1||0
2|title|TEXT|1||0
3|body|TEXT|1||0

We can see the column names, types, and constraints for the posts table.

Benefits of Exploring Schemas with SQLite Show Tables

There are a few key benefits to taking the time to explore table schemas using SQLite Show Tables:

• Understand the database structure – By seeing which tables exist and their columns/types, you better understand how the database is organized.
• Debug issues – If queries are not working as expected, comparing the actual schema vs. your assumptions can uncover issues.
• Optimize queries – Knowing which columns are indexed can help write faster queries by filtering on indexed columns.
• Ensure stability – Reviewing schemas after big changes helps ensure alter table statements are executed correctly.
• Document the database – The schema often serves as documentation if original DB creation scripts are unavailable.
• Identify bottlenecks – Examining column types and constraints may reveal bottlenecks that can be optimized.

Using SQLite’s schema introspection capabilities makes database development and maintenance much easier.

SQLite Show Tables GUI Tools

In addition to the command line shell, many graphical SQLite tools provide easy ways to view table schemas visually:

• DB Browser for SQLite – Open source GUI for managing SQLite databases. Shows a list of tables along with their columns, types, and other properties.
• SQLite Studio – Commercial multi-platform SQLite GUI. Includes a database explorer pane for browsing tables.
• SQLite Expert – Commercial Windows program for managing SQLite databases. Features a schema viewer and visual query builder.
• DBeaver – Open source universal database tool that supports SQLite, among many others. Provides schema explorer and SQL editor.

These tools can supplement the command line when you need a more graphical interface for exploring and managing SQLite databases.

Advanced Techniques for Using SQLite Show Tables

In addition to the basic commands we’ve covered, there are some more advanced techniques for querying and understanding SQLite database schemas:

List attached databases

SQLite allows attaching multiple databases to a single connection which can be queried as one. You can use PRAGMA database_list to see all attached databases:

PRAGMA database_list;

This will list the file path for each attached database. You can then query tables across all attached databases.

Generate CREATE table statement

While .schema TABLE_NAME shows the CREATE statement for an existing table, you can also generate a CREATE statement for a table using:

SELECT sql FROM sqlite_master
WHERE type='table' AND name='table_name';

This extracts the SQL used to create the table from the sqlite_master catalog.

Export schema as SQL

To export the entire schema for a database as SQL create statements, you can query the sqlite_master table and output the SQL:

SELECT sql FROM sqlite_master;

This will output all CREATE statements for tables and other objects in the database. You can save the output to a SQL file to port the schema to another database.

Query column metadata

SQLite stores additional metadata on columns in a separate table called pragma_table_info(). You can join this table to see metadata directly tied to each column:

SELECT 
  m.name AS table_name,
  i.name AS column_name,
  i.type AS data_type,
  i.notnull AS not_null
FROM 
  sqlite_master AS m
  JOIN pragma_table_info(m.name) AS i;

This provides an alternative way to query schema data that requires explicitly joining the metadata table.

Conclusion

SQLite offers several simple but powerful commands to show tables and view database schemas. Using .tables, .schema, and PRAGMA table_info() provides great insight into your database structure and aids in development and debugging.

Understanding how to access table definitions programmatically is an indispensable skill for any SQLite developer. Whether you use the interactive shell, command line invocation, or GUI tools, being able to explore and query table schemas will improve your ability to work with SQLite databases.