SQLite Describe Table

Created with Sketch.

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

 

Then, issue the following command:

.schema table_name

 

For example, the following command shows the statement that created the albums table:

.schema albums

 

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]);

 

Another way to show the structure of a table is to use the PRAGMA command. To do it, you use the following command to format the output:

.header on
.mode column

 

And use the PRAGMA command as follows:

pragma table_info('albums');

Code language: JavaScript (javascript)

The following picture shows the output:

cid name type notnull dflt_value pk
--- -------- ------------- ------- ---------- --
0 AlbumId INTEGER 1 1
1 Title NVARCHAR(160) 1 0
2 ArtistId INTEGER 1 0

 

Getting the structure of a table using the SQL statement

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

SELECT sql
FROM sqlite_schema
WHERE name = 'albums';

 

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
)

 

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

Leave a Reply

Your email address will not be published. Required fields are marked *