Copy table structure to new table in sqlite3

You could use a command like this:

CREATE TABLE copied AS SELECT * FROM mytable WHERE 0

but due to SQLite's dynamic typing, most type information would be lost.

If you need just a table that behaves like the original, i.e., has the same number and names of columns, and can store the same values, this is enough.

If you really need the type information exactly like the original, you can read the original SQL CREATE TABLE statement from the sqlite_master table, like this:

SELECT sql FROM sqlite_master WHERE type='table' AND name='mytable'

SQLite cannot clone table with PK, defaults and indices.

Hacking by another tool is necessary.

In shell, replace the table name by sed.

sqlite3 dbfile '.schema oldtable' | sed '1s/oldtable/newtable/' | sqlite3 dbfile

And you can check new table.

sqlite3 dbfile '.schema newtable'

Primary key, defaults and indices will be reserved.

I hope this command can help you.

sqlite> .schema

CREATE TABLE [About](  [id],  [name],  [value]);

.schema command will give you structure of About-table how it could be made by programming SQLite interpreter by hand, typing in commands.

Paste in and execute, the CREATE block giving the table new name:

sqlite> CREATE TABLE [AboutToo](  [id],  [name],  [value]);

.tables command now will show you have two tables, old and new, "copied".

sqlite> .tablesAbout     AboutToo

p.s. sqlite> is command prompt you get in console after launching SQLite.exe interpreter. To get it go to www.sqlite.org