How to delete or add column in SQLITE?

Question

I want to delete or add column in sqlite database

I am using following query to delete column.

ALTER TABLE TABLENAME DROP COLUMN COLUMNNAME

But it gives error

System.Data.SQLite.SQLiteException: SQLite error
near "DROP": syntax error

Cristalcristate

Possible duplicate of Delete column from SQLite table – Horning

The accepted answer is outdated, please consider accepting the answer by Lukasz Szozda, https://mcmap.net/q/93030/-how-to-delete-or-add-column-in-sqlite . I almost missed the fact that it is possible by just looking at the accepted, top ranking answer. – Peignoir

Answer 1

OUTDATED: While this answer was perfectly valid at time of writing, the information it contains is outdated. There is a better way to do this now, see this answer instead. The answer has been preserved for historic reasons.

---

ALTER TABLE SQLite

SQLite supports a limited subset of ALTER TABLE. The ALTER TABLE command in SQLite allows the user to rename a table or to add a new column to an existing table. It is not possible to rename a column, remove a column, or add or remove constraints from a table.

You can:

1. create new table as the one you are trying to change,
2. copy all data,
3. drop old table,
4. rename the new one.

Manifold

https://mcmap.net/q/94197/-drop-column-from-sqlite-table gives a basic example for performing the task. – Membranous

Before doing this sequence and in cases where there are external tables referring to this table one must call PRAGMA foreign_keys=OFF. In this case, after doing this sequence one must call PRAGMA foreign_keys=ON in order to re-enable foreign keys. – Deerstalker

How do you also copy the freoign key and indices as well? – Ropy

as long as you do a create new table first instead of a create from select, it will have all those things. – Ashmead

Could you update your link to documentation because website is now in https => sqlite.org/lang_altertable.html – Office

In newer SQLite versions, RENAME COLUMN is supported. 🎉 sqlite.org/releaselog/3_25_0.html – Stampede

This answer is outdated - there is now support for ALTER TABLE tablename DROP COLUMN columnname. See the documentation. – Peignoir

As Magnus W commented, recent versions support DROP COLUMN - see this answer. – Bodoni

Adding to @Deerstalker comment, after simulating the ALTER table command it would also be a good idea to run PRAGMA foreign_key_check to make sure your foreign keys are still consistent in the new structure of your database. – Honk

Answer 2

SQLite 3.35.0 introduced support for ALTER TABLE DROP COLUMN.

ALTER TABLE

The DROP COLUMN syntax is used to remove an existing column from a table. The DROP COLUMN command removes the named column from the table, and also rewrites the entire table to purge the data associated with that column. The DROP COLUMN command only works if the column is not referenced by any other parts of the schema and is not a PRIMARY KEY and does not have a UNIQUE constraint.

The following syntax will be valid:

ALTER TABLE <TABLENAME> DROP COLUMN <COLUMNNAME>;
ALTER TABLE <TABLENAME> DROP <COLUMNNAME>;

Providential

Cool but when will that syntax be accepted in android? – Lampkin

@Lampkin It seems the higest version as of today is 3.32.2. I have no knowledge when it will be upgraded. – Providential

Here you can find out which version of Sqlite is used by Android API levels: developer.android.com/reference/android/database/sqlite/… – Emelina

So according to sqlite versions mentioned in documentations, we can't use this syntax on Android devices!! – Emelina

It's supported in API 34 now (SQLite version 3.39.2), but until your min sdk version is 34 you'll need to do the hacky method instead, as devices on Android 13 and lower will crash. – Spiritualize

Answer 3

I've wrote a Java implementation based on the Sqlite's recommended way to do this:

private void dropColumn(SQLiteDatabase db,
        ConnectionSource connectionSource,
        String createTableCmd,
        String tableName,
        String[] colsToRemove) throws java.sql.SQLException {

    List<String> updatedTableColumns = getTableColumns(tableName);
    // Remove the columns we don't want anymore from the table's list of columns
    updatedTableColumns.removeAll(Arrays.asList(colsToRemove));

    String columnsSeperated = TextUtils.join(",", updatedTableColumns);

    db.execSQL("ALTER TABLE " + tableName + " RENAME TO " + tableName + "_old;");

    // Creating the table on its new format (no redundant columns)
    db.execSQL(createTableCmd);

    // Populating the table with the data
    db.execSQL("INSERT INTO " + tableName + "(" + columnsSeperated + ") SELECT "
            + columnsSeperated + " FROM " + tableName + "_old;");
    db.execSQL("DROP TABLE " + tableName + "_old;");
}

To get the table's column, I used the "PRAGMA table_info":

public List<String> getTableColumns(String tableName) {
    ArrayList<String> columns = new ArrayList<String>();
    String cmd = "pragma table_info(" + tableName + ");";
    Cursor cur = getDB().rawQuery(cmd, null);

    while (cur.moveToNext()) {
        columns.add(cur.getString(cur.getColumnIndex("name")));
    }
    cur.close();

    return columns;
}

I actually wrote about it on my blog, you can see more explanations there:

http://udinic.wordpress.com/2012/05/09/sqlite-drop-column-support/

Ballade

this is pretty slow though isnt it? for big data tables? – Millur

It would be better if this was done in a single transaction, rather than potentially allowing other code to see things in a transitional state. – Bertrando

This code i usually run when upgrading the DB, where other code is not running simultaneously. You can create a transaction and do all those commands in it. – Ballade

I'm pretty sure that if you use this solution the columns on your result table will be completely bare - no type information, PK, FK, default values, unique or check constraints will remain. All it imports to the new table is the column name. Furthermore, since it doesn't disable foreign keys before running, data in other tables could get screwed up too. – Reverso

@Reverso good comment. Do you have an idea how to support such thing? I guess we can use the PRAGMA directive to retrieve FK info and apply it on the created table. – Ballade

@Ballade I'm actually working on a pysqlite solution that does it all, but it's huge :). FKs are easy, if they're enforced just disable before the "colremove" operation and then reenable after. Pragmas can get you types, PKs, indexes, uniques, defaults. Check constraints are the pain in the ass, I'm parsing sqlite_master output for that, but a valid solution has to watch out for table and column names with "check" in the string among other things. Will post back when the solution is finished. – Reverso

Alternatively, instead of doing an INSERT statement, you can also create the new table by doing a "CREAT TABLE" + tableName + "AS SELECT " + columnsSeperated + " FROM " + tableName + "_old;" – Greerson

Answer 4

As others have pointed out

It is not possible to rename a column, remove a column, or add or remove constraints from a table.

source : http://www.sqlite.org/lang_altertable.html

While you can always create a new table and then drop the older one. I will try to explain this workaround with an example.

sqlite> .schema
CREATE TABLE person(
 id INTEGER PRIMARY KEY, 
 first_name TEXT,
 last_name TEXT, 
 age INTEGER, 
 height INTEGER
);
sqlite> select * from person ; 
id          first_name  last_name   age         height    
----------  ----------  ----------  ----------  ----------
0           john        doe         20          170       
1           foo         bar         25          171       

Now you want to remove the column height from this table.

Create another table called new_person

sqlite> CREATE TABLE new_person(
   ...>  id INTEGER PRIMARY KEY, 
   ...>  first_name TEXT, 
   ...>  last_name TEXT, 
   ...>  age INTEGER 
   ...> ) ; 
sqlite> 

Now copy the data from the old table

sqlite> INSERT INTO new_person
   ...> SELECT id, first_name, last_name, age FROM person ;
sqlite> select * from new_person ;
id          first_name  last_name   age       
----------  ----------  ----------  ----------
0           john        doe         20        
1           foo         bar         25        
sqlite>

Now Drop the person table and rename new_person to person

sqlite> DROP TABLE IF EXISTS person ; 
sqlite> ALTER TABLE new_person RENAME TO person ;
sqlite>

So now if you do a .schema, you will see

sqlite>.schema
CREATE TABLE "person"(
 id INTEGER PRIMARY KEY, 
 first_name TEXT, 
 last_name TEXT, 
 age INTEGER 
);

Tachycardia

What about foreign references? Oracle would complain if you delete a table that some other table is using. – Menjivar

I can tell that you are a real programmer. You ran out of names right after john doe and went straight to "foo bar" :) – Ostend

CREATE TABLE new_person AS SELECT id, first_name, last_name, age FROM person; – Milicent

It's possible to drop columns now - see this answer – Bodoni

Answer 5

DB Browser for SQLite allows you to add or drop columns.

In the main view, tab Database Structure, click on the table name. A button Modify Table gets enabled, which opens a new window where you can select the column/field and remove it.

Colly

I got to say this is the easiest if you are working with a single DB and can just copy it. +1 – Lagrange

Worked like a charm. No hassle. Just remember to hit: File->Write changes at the end – Realist

I wonder how it does, if the underlying sqlite3 engine don't support ALTER TABLE table_name DROP COLUMN column_name – Effloresce

Under the hood, DB Browser for SQLite use the same technique of create temp table / insert into temp table from original table / drop original table. Foreign key validation is deferred with PRAGMA defer_foreign_keys – Pacorro

Answer 6

At one time this was not directly supported and you would need to follow a four-step process: (1) create a temporary_table, (2) copy the data, (3) drop the old table, and then (4) rename the temporary_table.

But now that these features are supported, all you need to do is upgrade SQLite.

• 3.35.0 from 2021-03-12 adds ALTER TABLE DROP COLUMN
• 3.25.0 from 2018-09-15 added ALTER TABLE RENAME COLUMN
• 3.2.0 from way back in 2005-03-21 added ALTER TABLE ADD COLUMN

Note that there are still some edge cases where these may not work, e.g., you cannot drop a primary key column. See the documentation for more details. When these ALTER TABLE … COLUMN statements do not work, you can fall back to the four-step process.

By the way the four-step process is really a twelve-step process in the docs. But four of those steps are really important, easy to get wrong, and specifically called out in those docs.

Gavage

Answer 7

http://www.sqlite.org/lang_altertable.html

As you can see in the diagram, only ADD COLUMN is supported. There is a (kinda heavy) workaround, though: http://www.sqlite.org/faq.html#q11

Afebrile

The "workaround" just doesn't seem correct. You are losing column types, constraints, indices and what not. – Hangup

Answer 8

We cannot drop a specific column in SQLite 3. See the FAQ.

Standoffish

Answer 9

As others have pointed out, sqlite's ALTER TABLE statement does not support DROP COLUMN, and the standard recipe to do this does not preserve constraints & indices.

Here's some python code to do this generically, while maintaining all the key constraints and indices.

Please back-up your database before using! This function relies on doctoring the original CREATE TABLE statement and is potentially a bit unsafe - for instance it will do the wrong thing if an identifier contains an embedded comma or parenthesis.

If anyone would care to contribute a better way to parse the SQL, that would be great!

UPDATE I found a better way to parse using the open-source sqlparse package. If there is any interest I will post it here, just leave a comment asking for it ...

import re
import random

def DROP_COLUMN(db, table, column):
    columns = [ c[1] for c in db.execute("PRAGMA table_info(%s)" % table) ]
    columns = [ c for c in columns if c != column ]
    sql = db.execute("SELECT sql from sqlite_master where name = '%s'" 
        % table).fetchone()[0]
    sql = format(sql)
    lines = sql.splitlines()
    findcol = r'\b%s\b' % column
    keeplines = [ line for line in lines if not re.search(findcol, line) ]
    create = '\n'.join(keeplines)
    create = re.sub(r',(\s*\))', r'\1', create)
    temp = 'tmp%d' % random.randint(1e8, 1e9)
    db.execute("ALTER TABLE %(old)s RENAME TO %(new)s" % { 
        'old': table, 'new': temp })
    db.execute(create)
    db.execute("""
        INSERT INTO %(new)s ( %(columns)s ) 
        SELECT %(columns)s FROM %(old)s
    """ % { 
        'old': temp,
        'new': table,
        'columns': ', '.join(columns)
    })  
    db.execute("DROP TABLE %s" % temp)

def format(sql):
    sql = sql.replace(",", ",\n")
    sql = sql.replace("(", "(\n")
    sql = sql.replace(")", "\n)")
    return sql

Whiffet

Does it maintain foreign keys to the table as well? – Catton

@LasseV.Karlsen I did some tests and it should maintain foreign key constraints as these seem to be enforced by table name. – Whiffet

How can I run it from Java? – Menjivar

Answer 10

I rewrote the @Udinic answer so that the code generates table creation query automatically. It also doesn't need ConnectionSource. It also has to do this inside a transaction.

public static String getOneTableDbSchema(SQLiteDatabase db, String tableName) {
    Cursor c = db.rawQuery(
            "SELECT * FROM `sqlite_master` WHERE `type` = 'table' AND `name` = '" + tableName + "'", null);
    String result = null;
    if (c.moveToFirst()) {
        result = c.getString(c.getColumnIndex("sql"));
    }
    c.close();
    return result;
}

public List<String> getTableColumns(SQLiteDatabase db, String tableName) {
    ArrayList<String> columns = new ArrayList<>();
    String cmd = "pragma table_info(" + tableName + ");";
    Cursor cur = db.rawQuery(cmd, null);

    while (cur.moveToNext()) {
        columns.add(cur.getString(cur.getColumnIndex("name")));
    }
    cur.close();

    return columns;
}

private void dropColumn(SQLiteDatabase db, String tableName, String[] columnsToRemove) {
    db.beginTransaction();
    try {
        List<String> columnNamesWithoutRemovedOnes = getTableColumns(db, tableName);
        // Remove the columns we don't want anymore from the table's list of columns
        columnNamesWithoutRemovedOnes.removeAll(Arrays.asList(columnsToRemove));

        String newColumnNamesSeparated = TextUtils.join(" , ", columnNamesWithoutRemovedOnes);
        String sql = getOneTableDbSchema(db, tableName);
        // Extract the SQL query that contains only columns
        String oldColumnsSql = sql.substring(sql.indexOf("(")+1, sql.lastIndexOf(")"));

        db.execSQL("ALTER TABLE " + tableName + " RENAME TO " + tableName + "_old;");
        db.execSQL("CREATE TABLE `" + tableName + "` (" + getSqlWithoutRemovedColumns(oldColumnsSql, columnsToRemove)+ ");");
        db.execSQL("INSERT INTO " + tableName + "(" + newColumnNamesSeparated + ") SELECT " + newColumnNamesSeparated + " FROM " + tableName + "_old;");
        db.execSQL("DROP TABLE " + tableName + "_old;");
        db.setTransactionSuccessful();
    } catch {
        //Error in between database transaction 
    } finally {
        db.endTransaction();
    }


}

Navigate

Answer 11

I have improved user2638929 answer and now it can preserves column type, primary key, default value etc.

public static void dropColumns(SQLiteDatabase database, String tableName, Collection<String> columnsToRemove){
    List<String> columnNames = new ArrayList<>();
    List<String> columnNamesWithType = new ArrayList<>();
    List<String> primaryKeys = new ArrayList<>();
    String query = "pragma table_info(" + tableName + ");";
    Cursor cursor = database.rawQuery(query,null);
    while (cursor.moveToNext()){
        String columnName = cursor.getString(cursor.getColumnIndex("name"));

        if (columnsToRemove.contains(columnName)){
            continue;
        }

        String columnType = cursor.getString(cursor.getColumnIndex("type"));
        boolean isNotNull = cursor.getInt(cursor.getColumnIndex("notnull")) == 1;
        boolean isPk = cursor.getInt(cursor.getColumnIndex("pk")) == 1;

        columnNames.add(columnName);
        String tmp = "`" + columnName + "` " + columnType + " ";
        if (isNotNull){
            tmp += " NOT NULL ";
        }

        int defaultValueType = cursor.getType(cursor.getColumnIndex("dflt_value"));
        if (defaultValueType == Cursor.FIELD_TYPE_STRING){
            tmp += " DEFAULT " + "\"" + cursor.getString(cursor.getColumnIndex("dflt_value")) + "\" ";
        }else if(defaultValueType == Cursor.FIELD_TYPE_INTEGER){
            tmp += " DEFAULT " + cursor.getInt(cursor.getColumnIndex("dflt_value")) + " ";
        }else if (defaultValueType == Cursor.FIELD_TYPE_FLOAT){
            tmp += " DEFAULT " + cursor.getFloat(cursor.getColumnIndex("dflt_value")) + " ";
        }
        columnNamesWithType.add(tmp);
        if (isPk){
            primaryKeys.add("`" + columnName + "`");
        }
    }
    cursor.close();

    String columnNamesSeparated = TextUtils.join(", ", columnNames);
    if (primaryKeys.size() > 0){
        columnNamesWithType.add("PRIMARY KEY("+ TextUtils.join(", ", primaryKeys) +")");
    }
    String columnNamesWithTypeSeparated = TextUtils.join(", ", columnNamesWithType);

    database.beginTransaction();
    try {
        database.execSQL("ALTER TABLE " + tableName + " RENAME TO " + tableName + "_old;");
        database.execSQL("CREATE TABLE " + tableName + " (" + columnNamesWithTypeSeparated + ");");
        database.execSQL("INSERT INTO " + tableName + " (" + columnNamesSeparated + ") SELECT "
                + columnNamesSeparated + " FROM " + tableName + "_old;");
        database.execSQL("DROP TABLE " + tableName + "_old;");
        database.setTransactionSuccessful();
    }finally {
        database.endTransaction();
    }
}

PS. I used here android.arch.persistence.db.SupportSQLiteDatabase, but you can easyly modify it for use android.database.sqlite.SQLiteDatabase

Blastosphere

This works very well. Do you know perhaps how to rename a column instead, using the same generic way? If so, here's an example of such a question: https://mcmap.net/q/94199/-sqlite-syntax-error-code-1-when-renaming-a-column-name/878126 – Dismuke

Note that you should check if the table you rename to already exists on RENAME TO. Here it is with this fix : https://mcmap.net/q/93030/-how-to-delete-or-add-column-in-sqlite – Dismuke

I think the defaultValueType become String type for Integer for some reason. Maybe better to avoid using cursor.getType(cursor.getColumnIndex("dflt_value")) – Dismuke

Note that this doesn't handle foreign keys – Leastways

Answer 12

I guess what you are wanting to do is database migration. 'Drop'ping a column does not exist in SQLite. But you can however, add an extra column by using the ALTER table query.

Device

Answer 13

you can use Sqlitebrowser. In the browser mode, for the respective database and the table, under the tab -database structure,following the option Modify Table, respective column could be removed.

Wyatt

Answer 14

At least as of version 3.37.0, sqlite3 does support DROP COLUMN

Mayst

Answer 15

You can use the SQlite Administrator for changing the column names. Right Click on Table name and select Edit Table.Here you will find the table structure and you can easily rename it.

Triaxial

Answer 16

As SQLite has limited support to ALTER TABLE so you can only ADD column at end of the table OR CHANGE TABLE_NAME in SQLite.

Here is the Best Answer of HOW TO DELETE COLUMN FROM SQLITE?

visit Delete column from SQLite table

Penicillate

Answer 17

As an alternative:

If you have a table with schema

CREATE TABLE person(
  id INTEGER PRIMARY KEY,
  first_name TEXT,
  last_name TEXT,
  age INTEGER,
  height INTEGER
);

you can use a CREATE TABLE...AS statement like CREATE TABLE person2 AS SELECT id, first_name, last_name, age FROM person;, i.e. leave out the columns you don't want. Then drop the original person table and rename the new one.

Note this method produces a table has no PRIMARY KEY and no constraints. To preserve those, utilize the methods others described to create a new table, or use a temporary table as an intermediate.

Pocahontas

Answer 18

This answer to a different question is oriented toward modifying a column, but I believe a portion of the answer could also yield a useful approach if you have lots of columns and don't want to retype most of them by hand for your INSERT statement:

https://stackoverflow.com/a/10385666

You could dump your database as described in the link above, then grab the "create table" statement and an "insert" template from that dump, then follow the instructions in the SQLite FAQ entry "How do I add or delete columns from an existing table in SQLite." (FAQ is linked elsewhere on this page.)

Dogfish

Actually, I just realized that the dump doesn't include column names in the insert by default. So it might be equally good just to use the .schema pragma to grab column names, since you'll then need to delete type declarations either way. – Dogfish

Answer 19

Implementation in Python based on information at http://www.sqlite.org/faq.html#q11.

import sqlite3 as db
import random
import string

QUERY_TEMPLATE_GET_COLUMNS = "PRAGMA table_info(@table_name)"
QUERY_TEMPLATE_DROP_COLUMN = """
  BEGIN TRANSACTION;
  CREATE TEMPORARY TABLE @tmp_table(@columns_to_keep);
  INSERT INTO @tmp_table SELECT @columns_to_keep FROM @table_name;
  DROP TABLE @table_name;
  CREATE TABLE @table_name(@columns_to_keep);
  INSERT INTO @table_name SELECT @columns_to_keep FROM @tmp_table;
  DROP TABLE @tmp_table;
  COMMIT;
"""

def drop_column(db_file, table_name, column_name):
    con = db.connect(db_file)
    QUERY_GET_COLUMNS = QUERY_TEMPLATE_GET_COLUMNS.replace("@table_name", table_name)
    query_res = con.execute(QUERY_GET_COLUMNS).fetchall()
    columns_list_to_keep = [i[1] for i in query_res if i[1] != column_name]
    columns_to_keep = ",".join(columns_list_to_keep)
    tmp_table = "tmp_%s" % "".join(random.sample(string.ascii_lowercase, 10))
    QUERY_DROP_COLUMN = QUERY_TEMPLATE_DROP_COLUMN.replace("@table_name", table_name)\
        .replace("@tmp_table", tmp_table).replace("@columns_to_keep", columns_to_keep)
    con.executescript(QUERY_DROP_COLUMN)
    con.close()

drop_column(DB_FILE, TABLE_NAME, COLUMN_NAME)

This script first makes random temporary table and inserts data of only necessary columns except the one that will will be dropped. Then restores the original table based on the temporary table and drops the temporary table.

Mieshamiett

Answer 20

My solution, only need to call this method.

public static void dropColumn(SQLiteDatabase db, String tableName, String[] columnsToRemove) throws java.sql.SQLException {
    List<String> updatedTableColumns = getTableColumns(db, tableName);
    updatedTableColumns.removeAll(Arrays.asList(columnsToRemove));
    String columnsSeperated = TextUtils.join(",", updatedTableColumns);

    db.execSQL("ALTER TABLE " + tableName + " RENAME TO " + tableName + "_old;");
    db.execSQL("CREATE TABLE " + tableName + " (" + columnsSeperated + ");");
    db.execSQL("INSERT INTO " + tableName + "(" + columnsSeperated + ") SELECT "
            + columnsSeperated + " FROM " + tableName + "_old;");
    db.execSQL("DROP TABLE " + tableName + "_old;");
}

And auxiliary method to get the columns:

public static List<String> getTableColumns(SQLiteDatabase db, String tableName) {
    ArrayList<String> columns = new ArrayList<>();
    String cmd = "pragma table_info(" + tableName + ");";
    Cursor cur = db.rawQuery(cmd, null);

    while (cur.moveToNext()) {
        columns.add(cur.getString(cur.getColumnIndex("name")));
    }
    cur.close();

    return columns;
}

Outclass

This method does not keep colunm type, so i posted modified version of your code – Blastosphere

Answer 21

public void DeleteColFromTable(String DbName, String TableName, String ColName){
    SQLiteDatabase db = openOrCreateDatabase(""+DbName+"", Context.MODE_PRIVATE, null);
    db.execSQL("CREATE TABLE IF NOT EXISTS "+TableName+"(1x00dff);");
    Cursor c = db.rawQuery("PRAGMA table_info("+TableName+")", null);
    if (c.getCount() == 0) {

    } else {
        String columns1 = "";
        String columns2 = "";
        while (c.moveToNext()) {
            if (c.getString(1).equals(ColName)) {
            } else {
                columns1 = columns1 + ", " + c.getString(1) + " " + c.getString(2);
                columns2 = columns2 + ", " + c.getString(1);
            }
            if (c.isLast()) {
                db.execSQL("CREATE TABLE IF NOT EXISTS DataBackup (" + columns1 + ");");
                db.execSQL("INSERT INTO DataBackup SELECT " + columns2 + " FROM "+TableName+";");
                db.execSQL("DROP TABLE "+TableName+"");
                db.execSQL("ALTER TABLE DataBackup RENAME TO "+TableName+";");
            }
        }
    }
}

and just call a method

DeleteColFromTable("Database name","Table name","Col name which want to delete");

Bichromate

Answer 22

Kotlin solution, based on here , but also:

1. Ensures the temporary table doesn't already exist
2. Has a fix of checking the type for the default value, as it returns String type when it's an Integer (reported about this issue here).
3. Avoids doing anything if the columns that you wish to remove don't exist anyway.

object DbUtil {
    /** https://mcmap.net/q/93030/-how-to-delete-or-add-column-in-sqlite */
    @JvmStatic
    fun dropColumns(database: SQLiteDatabase, tableName: String,
        columnsToRemove: Collection<String>) {
        val columnNames: MutableList<String> = ArrayList()
        val columnNamesWithType: MutableList<String> = ArrayList()
        val primaryKeys: MutableList<String> = ArrayList()
        val query = "pragma table_info($tableName);"
        val cursor = database.rawQuery(query, null)
        val columnDefaultIndex = cursor.getColumnIndex("dflt_value")
        val columnNameIndex = cursor.getColumnIndex("name")
        val columnTypeIndex = cursor.getColumnIndex("type")
        val columnNotNullIndex = cursor.getColumnIndex("notnull")
        val columnPrimaryKeyIndex = cursor.getColumnIndex("pk")
        val sb = StringBuilder()
        var foundColumnsToRemove = false
        while (cursor.moveToNext()) {
            val columnName = cursor.getString(columnNameIndex)
            if (columnsToRemove.contains(columnName)) {
                foundColumnsToRemove = true
                continue
            }
            val columnType = cursor.getString(columnTypeIndex)
            val isNotNull = cursor.getInt(columnNotNullIndex) == 1
            val isPrimaryKey = cursor.getInt(columnPrimaryKeyIndex) == 1
            columnNames.add(columnName)
            sb.clear()
            sb.append("`$columnName` $columnType ")
            if (isNotNull)
                sb.append(" NOT NULL ")
            if (cursor.getType(columnDefaultIndex) != Cursor.FIELD_TYPE_NULL) {
                //has default value
                when (columnType.uppercase()) {
                    "INTEGER" -> sb.append(" DEFAULT ${cursor.getInt(columnDefaultIndex)} ")
                    "TEXT" -> sb.append(" DEFAULT \"${cursor.getString(columnDefaultIndex)}\" ")
                    "REAL" -> sb.append(" DEFAULT ${cursor.getFloat(columnDefaultIndex)} ")
                }
            }
            columnNamesWithType.add(sb.toString())
            if (isPrimaryKey)
                primaryKeys.add("`$columnName`")
        }
        cursor.close()
        if (!foundColumnsToRemove)
            return
        val columnNamesSeparated = TextUtils.join(", ", columnNames)
        if (primaryKeys.size > 0)
            columnNamesWithType.add("PRIMARY KEY(${TextUtils.join(", ", primaryKeys)})")
        val columnNamesWithTypeSeparated = TextUtils.join(", ", columnNamesWithType)
        database.beginTransaction()
        try {
            var newTempTableName: String
            var counter = 0
            while (true) {
                newTempTableName = "${tableName}_old_$counter"
                if (!isTableExists(database, newTempTableName))
                    break
                ++counter
            }
            database.execSQL("ALTER TABLE $tableName RENAME TO $newTempTableName;")
            database.execSQL("CREATE TABLE $tableName ($columnNamesWithTypeSeparated);")
            database.execSQL(
                "INSERT INTO $tableName ($columnNamesSeparated) SELECT $columnNamesSeparated FROM $newTempTableName;")
            database.execSQL("DROP TABLE ${newTempTableName};")
            database.setTransactionSuccessful()
        } finally {
            database.endTransaction()
        }
    }

    @JvmStatic
    fun isTableExists(database: SQLiteDatabase, tableName: String): Boolean {
        database.rawQuery(
            "select DISTINCT tbl_name from sqlite_master where tbl_name = '$tableName'", null)
            ?.use {
                return it.count > 0
            } ?: return false
    }
}

Dismuke

Answer 23

You can also now use DB browser for SQLite to manipulate columns

Oraorabel