This article contains three ways to generate an SQL script from an existing table in SQLite.

All three methods generate the CREATE TABLE script, but the last method also generates the INSERT statements for inserting the data.

The .schema Command

If you only want to script the table structure (i.e. without its data), you can use the .schema command.

To do this, simply use .schema followed by the name of the table you want to script.

Here’s an example:

.schema Genre

Result:

CREATE TABLE [Genre]
(
    [GenreId] INTEGER  NOT NULL,
    [Name] NVARCHAR(120),
    CONSTRAINT [PK_Genre] PRIMARY KEY  ([GenreId])
);

In this case I scripted the Genre table.

You can also use pattern matching with the .schema command.

For example, the following command returns the same result as the previous one:

.schema Gen%

You can also use the .schema command to script the whole database. To do this, simply use the command without an argument. Like this:

.schema

By default, .schema shows the schema for all attached databases. If you only want to see the schema for a single database, you can add an argument to specify the database/s you’re interested in.

For example:

.schema main.*

The sqlite_master Table

An alternative to .schema is to directly query the sqlite_master table. This table contains a column called sql, which contains the SQL used to create the table.

Here’s an example:

SELECT sql FROM sqlite_master WHERE tbl_name = 'Genre';

Result:

CREATE TABLE [Genre]
(
    [GenreId] INTEGER  NOT NULL,
    [Name] NVARCHAR(120),
    CONSTRAINT [PK_Genre] PRIMARY KEY  ([GenreId])
)

Include Table Data

If you want to get all data, use the .dump command. This command scripts the CREATE TABLE statement as well as the INSERT statements for inserting all data into the table.

Here’s an example:

.dump Genre

Result:

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE [Genre]
(
    [GenreId] INTEGER  NOT NULL,
    [Name] NVARCHAR(120),
    CONSTRAINT [PK_Genre] PRIMARY KEY  ([GenreId])
);
INSERT INTO Genre VALUES(1,'Rock');
INSERT INTO Genre VALUES(2,'Jazz');
INSERT INTO Genre VALUES(3,'Metal');
INSERT INTO Genre VALUES(4,'Alternative & Punk');
INSERT INTO Genre VALUES(5,'Rock And Roll');
INSERT INTO Genre VALUES(6,'Blues');
INSERT INTO Genre VALUES(7,'Latin');
INSERT INTO Genre VALUES(8,'Reggae');
INSERT INTO Genre VALUES(9,'Pop');
INSERT INTO Genre VALUES(10,'Soundtrack');
INSERT INTO Genre VALUES(11,'Bossa Nova');
INSERT INTO Genre VALUES(12,'Easy Listening');
INSERT INTO Genre VALUES(13,'Heavy Metal');
INSERT INTO Genre VALUES(14,'R&B/Soul');
INSERT INTO Genre VALUES(15,'Electronica/Dance');
INSERT INTO Genre VALUES(16,'World');
INSERT INTO Genre VALUES(17,'Hip Hop/Rap');
INSERT INTO Genre VALUES(18,'Science Fiction');
INSERT INTO Genre VALUES(19,'TV Shows');
INSERT INTO Genre VALUES(20,'Sci Fi & Fantasy');
INSERT INTO Genre VALUES(21,'Drama');
INSERT INTO Genre VALUES(22,'Comedy');
INSERT INTO Genre VALUES(23,'Alternative');
INSERT INTO Genre VALUES(24,'Classical');
INSERT INTO Genre VALUES(25,'Opera');
COMMIT;