android - How to copy only the table structure?

Question

I want to create a database. It has up to 9 tables in future it may exceed. But the structure (number of columns and column name) of table is same for all tables. I know that there is an option to copy the structure of table and rename it and use it but I don't know how to do it exactly. Please give me the relevant answer so that it reduces my code.

Answer 1

You could extract the columns from the base table via PRAGMA table_info (<yourtable>)

e.g.

String sqlstr = " PRAGMA table_info (" + table_name + ")";
        Cursor csr = db.rawQuery(sqlstr, null);

The resultant cusors has rows with columns :-

cid (rowid)

name (name of the column)

type (column type e.g. INTEGER)

notnull (0 if can be null)

dflt_value (default value if one)

pk (0 if not part of the primary key otherwise 1,2... if part of the primary key)

You could then build the SQL from this.

---

Another alternative could be to extract the SQL used to create the table via :-

SELECT sql FROM sqlite_master WHERE type='table' AND name='<yourtable>'

You could then alter the resultant SQL, changing the base table name to the new table name.

---

A third option is to copy the table using something like

CREATE TABLE newtable AS SELECT * FROM basetable WHERE 0

However, this isn't a full comprehensive copy of the structure, e.g. primary keys aren't created.