SQLite Show Table Columns: A Detailed Guide

SQLite is a popular open-source database that is lightweight, easy to use, and stores data in a single file. When working with SQLite, it is often useful to view the column names and details of a table. This allows you to understand the structure of your data and write accurate queries.

In this guide, we will walk through the various ways to show table column information in SQLite. We will use examples based on a sample analytics database for a media site.

A Quick Intro to Our Sample Database

To demonstrate, we will use a database called website_analytics.db which stores data on visitors to a media site. It has a table called visitors with columns for:

• visitor_id – Primary key column
• first_name – First name of visitor
• last_name – Last name of visitor
• age – Age of visitor
• country – Country of visitor
• pages_visited – Number of pages visited in site

Let’s take a quick look at how we would create this sample table:

CREATE TABLE visitors (
  visitor_id INTEGER PRIMARY KEY, 
  first_name TEXT,
  last_name TEXT,
  age INTEGER,
  country TEXT,
  pages_visited INTEGER
);

Now let’s see how to view column information on this table.

Using the sqlite3 Command Line

The simplest way to view columns is by using the .schema command in sqlite3 command line:

sqlite> .schema visitors

CREATE TABLE visitors (
  visitor_id INTEGER PRIMARY KEY,
  first_name TEXT,
  last_name TEXT,
  age INTEGER,
  country TEXT,
  pages_visited INTEGER  
);

This prints out the SQL used to create the table, showing all column names and data types.

The PRAGMA table_info Command

For more detailed information on each column, use the PRAGMA table_info() command:

SELECT * FROM pragma_table_info('visitors');

Which returns:

cid         name        type        notnull     dflt_value  pk
----------  ----------  ----------  ----------  ----------  ----------
0           visitor_id  INTEGER     1                       1
1           first_name  TEXT                       0           0
2           last_name   TEXT                       0           0
3           age         INTEGER                   0           0 
4           country     TEXT                       0           0
5           pages_visited INTEGER                 0          0

This provides the column index, name, data type, whether it can be NULL, the default value, and if it is a primary key.

Querying the SQLite Master Table

The sqlite_master table contains metadata on all tables and can be queried:

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

Returns:

CREATE TABLE visitors (
  visitor_id INTEGER PRIMARY KEY,
  first_name TEXT,
  last_name TEXT, 
  age INTEGER,
  country TEXT,
  pages_visited INTEGER
)

This again prints out the table schema SQL, showing all column info.

Fetching a Single Column Name

You can also query just for a single column name like:

SELECT name FROM pragma_table_info('visitors') WHERE cid = 0; 

Which would return:

visitor_id

This is useful if you only need to check a particular column name.

Summary

And that covers the main ways to view column information for a table in SQLite! The key methods are:

• .schema in sqlite3 CLI
• PRAGMA table_info() query
• Querying sqlite_master
• Fetching specific columns from pragma_table_info()

Knowing how to check table schemas allows you to properly analyze and query your data in SQLite.