Copy table structure to new table in sqlite3

Question

Is there an easy way to copy an existing table structure to a new one? (dont need the data, only the structure -> like id INTEGER, name varchar(20) ...)

Thx

Answer 1

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' 

Answer 2

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.