sql - 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'