SQLite Describe Table

Summary: in this tutorial, you will learn about various ways to show the structure of a table in SQLite.

Getting the structure of a table via the SQLite command-line shell program

To find out the structure of a table via the SQLite command-line shell program, you follow these steps:

First, connect to a database via the SQLite command-line shell program:

> sqlite3 c:\sqlite\db\chinook.db
Code language: SQL (Structured Query Language) (sql)

Then, issue the following command:

.schema table_name
Code language: SQL (Structured Query Language) (sql)

For example, to show the statement that created the albums table, you use the following command:

sqlite> .schema albums
Code language: SQL (Structured Query Language) (sql)

Notice that there is no semicolon (;) after the table name. If you add a semicolon (;), the .schema will consider the albums; as the table name and returns nothing because the table albums; does not exist.

Here is the output:

CREATE TABLE IF NOT EXISTS "albums"
(
    [AlbumId] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    [Title] NVARCHAR(160)  NOT NULL,
    [ArtistId] INTEGER  NOT NULL,
    FOREIGN KEY ([ArtistId]) REFERENCES "artists" ([ArtistId])
                ON DELETE NO ACTION ON UPDATE NO ACTION
);
CREATE INDEX [IFK_AlbumArtistId] ON "albums" ([ArtistId]);
Code language: SQL (Structured Query Language) (sql)

Another way to show the structure of a table is to use the following PRAGMA command:

sqlite> .header on
sqlite> .mode column
sqlite> pragma table_info('albums');
Code language: SQL (Structured Query Language) (sql)

Note that the first two commands are used to format the output nicely.

The following picture shows the output:

Getting the structure of a table using the SQL statement

You can find the structure of a table by querying it from the sqlite_master table as follows:

SELECT sql 
FROM sqlite_master 
WHERE name = 'albums';
Code language: SQL (Structured Query Language) (sql)

Here is the output:

sql
------------
CREATE TABLE "albums"
(
    [AlbumId] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    [Title] NVARCHAR(160)  NOT NULL,
    [ArtistId] INTEGER  NOT NULL,
    FOREIGN KEY ([ArtistId]) REFERENCES "artists" ([ArtistId])
                ON DELETE NO ACTION ON UPDATE NO ACTION
)        
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to show the structure of a table in SQLite via command-line shell program or SQL statement.