How to Check in Sqlite Whether a Table Exists

How do I check in SQLite whether a table exists?

I missed that FAQ entry.

Anyway, for future reference, the complete query is:

SELECT name FROM sqlite_master WHERE type='table' AND name='{table_name}';

Where {table_name} is the name of the table to check.

Documentation section for reference: Database File Format. 2.6. Storage Of The SQL Database Schema

• This will return a list of tables with the name specified; that is, the cursor will have a count of 0 (does not exist) or a count of 1 (does exist)

sqlite - how to check if table exists before completing inserts?

To check that your table exists or not, you can use:

SELECT * FROM sqlite_master WHERE name ='myTable' and type='table'; 

Android with Sqlite - How to check if table exist and if empty

Hope,This will be helpful to you. It's working well!!!

public boolean isTableExists(String tableName, boolean openDb) {
    if(openDb) {
        if(mDatabase == null || !mDatabase.isOpen()) {
            mDatabase = getReadableDatabase();
        }

        if(!mDatabase.isReadOnly()) {
            mDatabase.close();
            mDatabase = getReadableDatabase();
        }
    }

    Cursor cursor = mDatabase.rawQuery("select DISTINCT tbl_name from sqlite_master where tbl_name = '"+tableName+"'", null);
    if(cursor!=null) {
        if(cursor.getCount()>0) {
                            cursor.close();
            return true;
        }
                    cursor.close();
    }
    return false;
}

Is it possible to check if an SQLite table exists.

This query will return the list of tables in the database

SELECT * FROM sqlite_master WHERE type = 'table';

You can filter it down to a single row for an "exists" check.

SELECT * FROM sqlite_master WHERE type = 'table' AND tbl_name = 'xyz';

How to check if a table within a .sqlite file exists using C#

ExecuteNonQuery() returns -1 for SELECT statements, so it would be inappropriate to use it in this case.

try using ExecuteReader(), which returns a SqliteDataReader class that contains the HasRows property, in which should be true in case of a non-empty result set.

the code:

SQLiteConnection connection = new SQLiteConnection("Data Source=filepath;Version=3");
sql = "SELECT name FROM sqlite_master WHERE type='table' AND name='{table_name}';"
SQLiteCommand command = new SQLiteCommand(sql, connection);

try 
{
  var result = command.ExecuteReader();

  if (result.HasRows) 
  {
    // INSERT statement here
  }

}
catch (Exception e) 
{
  throw e;
}

how to check if a table exists in C#

As far as I see it, you don't read from the reader:

[TestMethod]
public void LaCreationBaseMarche()
{
    string dataSource = "exemple.db";
    SqliteBase.CreerBase(dataSource);
    SQLiteConnection connection = new SQLiteConnection();

    connection.ConnectionString = "Data Source=" + dataSource;
    connection.Open();
    SQLiteCommand command = new SQLiteCommand(connection);
    command.CommandText = "SELECT name FROM exemple WHERE type = 'table' AND name = 'beispiel';";
    SQLiteDataReader reader = command.ExecuteReader();

    while (reader.Read())
    {
        Assert.Equals("beispiel", reader[0].ToString());
    }

    reader.Close();
    reader.Dispose();
    command.Dispose();
}

EDIT

A possible problem is the dataSource. You have to make sure, that both methods access the same location.

how to check if a table exists in sqlite

To start, and know the structure of the SQLite_Master table, getting the schema as shown below.

sqlite> .schema sqlite_master
CREATE TABLE sqlite_master (
  type text,
  name text,
  tbl_name text,
  rootpage integer,
  sql text
);

What I did next was to create a class structure to match

    private class CSQLite_Master
    {
        public string type { get; set; }
        public string name {get; set;}
        public string tbl_name { get; set; }
        public int rootpage { get; set; }
        public string sql { get; set; }
    }

From that, I used the connection .Query() feature like below

using (var db = new SQLiteConnection(YourPathToDatabase) )
{
   var tmp1 = db.Query<CSQLite_Master>(
        @"select * from sqlite_master where type = 'table' and name = ?", 
            new object[] { logname });
   if( tmp1 is null || tmp1.Count == 0 )
      return;

   // else we DO have an entry, and you can see all the parts of it
   // by doing basic debug inspection in the watch window 
}

The .Query executes whatever command available, and based on the class/structure <CSQLite_Master> in this case, the data is retrieved into a list of this object type. Now for the parameter. The "?" is a place-holder for the parameters to be provided.

The last parameter to the Query() call is an array of values added in the same sequence as the "?" place-holders. In this case, a list of objects new object[] { logname } and that list is created with the logname you prepared.

You should get either the record, or records if you did not include the AND name= portion of the query, it would return all table names, for you.

---

Related Topics