Printing the table structure of a SQLite Database in Android


I’m doing some Android app development, and as part of it, I hit some issues with the database.

My first plan was to download the database and open it in SQLite, but having to re-establish my ADB debug session each time I downoaded the file got annoying, so I decided to write a short snippet which dumps the name & CREATE TABLE schema of each table to the log:

SQLiteDatabase db = YourDbHelper.getWritableDatabase();
Log.d(this.getClass().getSimpleName(), db.getPath());
Cursor c = db.rawQuery("SELECT type, name, sql, tbl_name FROM sqlite_master", new String[]{});
c.moveToFirst();
while (!c.isAfterLast()){
        int count = c.getColumnCount();
        Log.d(this.getClass().getSimpleName(), Integer.toString(count));
        Log.d(this.getClass().getSimpleName(), c.getColumnNames().toString());
        for (int i = 0; i < count; i++){
                Log.d(this.getClass().getSimpleName(), c.getColumnName(i));
                Log.d(this.getClass().getSimpleName(), c.getString(i));
        }
        c.moveToNext();
}

This gives decently printed output for a quick and dirty script:

07-16 23:44:53.795/io.kyle.dev D/ProjectListActivity: name
07-16 23:44:53.795/io.kyle.dev D/ProjectListActivity: projects
07-16 23:44:53.795/io.kyle.dev D/ProjectListActivity: sql
07-16 23:44:53.795/io.kyle.dev D/ProjectListActivity: CREATE TABLE `projects` ( `_id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, `name` TEXT, `avatar` TEXT, `short_description` TEXT, `description` TEXT)

  1. No comments yet.
(will not be published)