how to print or copy the schema for a sqlite database

these assume a database file named ./mydb:

print the schema

sqlite shell

$ sqlite mydb
sqlite> .sch
sqlite> --   or...
sqlite> select sql from sqlite_master where sql not NULL;
sqlite> .q

shell command line

$ echo '.sch' | sqlite mydb
$ # or...
$ echo 'select sql from sqlite_master where sql not NULL;' | sqlite mydb

python

import os, sys, sqlite3
dbfile = "./mydb"
db = sqlite3.connect(dbfile)
cursor = db.cursor()
cursor.execute("select sql from sqlite_master where sql not NULL")
for row in cursor.fetchall():
        print row[1]
db.close()

tcl

package require sqlite3
sqlite3 db $env(HOME)/mydb
db eval {select sql from sqlite_master where sql not NULL} { db eval $sql }
db close

copy the schema

other methods left as an exercise for the reader…

tcl

package require sqlite3
sqlite3 db :memory:
db eval {attach "$env(HOME)/mydb" as disk}
db eval {select name from sqlite_master where type='table'} {
  db eval "insert into $name select _ from disk.$name"
}
db eval {detach disk}