How do I check in SQLite whether a table exists

Question

How do I  reliably  check in SQLite  whether a particular user table exists   I am not asking for unreliable ways like checking if a  select    on the table returned an error or not  is this even a good idea     The reason is like this   In my program  I need to create and then populate some tables if they do not exist already   If they do already exist  I need to update some tables   Should I take some other path instead to signal that the tables in question have already been created - say for example  by creating putting setting a certain flag in my program initialization settings file on disk or something   Or does my approach make sense

User · Answer

You can write the following query to check the table existance.

SELECT name FROM sqlite_master WHERE name='table_name'

Here 'table_name' is your table name what you created. For example

 CREATE TABLE IF NOT EXISTS country(country_id INTEGER PRIMARY KEY AUTOINCREMENT, country_code TEXT, country_name TEXT)"

and check

  SELECT name FROM sqlite_master WHERE name='country'

User · Answer

c   function checks db and all attached databases for existance of table and  optionally  column   bool exists sqlite3  db  string tbl  string col  1         sqlite3 stmt  stmt      bool b   sqlite3 prepare v2 db    select   col   from   tbl  c str        -1   amp stmt  0     SQLITE OK      sqlite3 finalize stmt       return b      Edit  Recently discovered the sqlite3 table column metadata function  Hence  bool exists sqlite3  db const char  tbl const char  col 0   return sqlite3 table column metadata db 0 tbl col 0 0 0 0 0   SQLITE OK

User · Answer

This is my code for SQLite Cordova   get columnNames  LastUpdate   function  data        if  data length  gt  0       In data you also have columnNames         console log  Table full              else           console log  Table empty                And the other one   function get columnNames tableName  callback        myDb transaction function  transaction            var query exec    SELECT name  sql FROM sqlite master WHERE type  table  AND name       tableName                transaction executeSql query exec      function  tx  results                var columnNames                   var len   results rows length              if  len gt 0                   var columnParts   results rows item 0  sql replace                      g    1   split             RegEx                 for  i in columnParts                        if  typeof columnParts i       string                           columnNames push columnParts i  split      0                                       callback columnNames                             else callback columnNames

User · Answer

Table exists or not in database in swift  func tableExists   tableName String  - gt  Bool           sqlStatement    SELECT name FROM sqlite master WHERE type  table  AND name    tableName            if sqlite3 prepare v2 database  sqlStatement -1   amp compiledStatement  nil     SQLITE OK               if sqlite3 step compiledStatement     SQLITE ROW                   return true                           else                   return false                                 else               return false                       sqlite3 finalize compiledStatement

User · Answer

Using a simple SELECT query is - in my opinion - quite reliable  Most of all it can check table existence in many different database types  SQLite   MySQL    SELECT 1 FROM table    It makes sense when you can use other reliable mechanism for determining if the query succeeded  for example  you query a database via QSqlQuery in Qt

User · Answer

You could try   SELECT name FROM sqlite master WHERE name  table name

User · Answer

The following code returns 1 if the table exists or 0 if the table does not exist   SELECT CASE WHEN tbl name    name  THEN 1 ELSE 0 END FROM sqlite master WHERE tbl name    name  AND type    table

User · Answer

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

User · Answer

Here s the function that I used     Given an SQLDatabase Object   db  public boolean exists String table        try            db query  SELECT   FROM     table            return true        catch  SQLException e             return false

User · Answer

Use  PRAGMA table info your table name   If the resulting table is empty then your table name doesn t exist  Documentation   PRAGMA schema table info table-name   This pragma returns one row for each column in the named table  Columns in the result set include the column name  data type  whether or not the column can be NULL  and the default value for the column  The  quot pk quot  column in the result set is zero for columns that are not part of the primary key  and is the index of the column in the primary key for columns that are part of the primary key  The table named in the table info pragma can also be a view   Example output  cid name type notnull dflt value pk 0 id INTEGER 0  1 1 json JSON 0  0 2 name TEXT 0  0

User · Answer

See this   SELECT name FROM sqlite master WHERE type  table  ORDER BY name

User · Answer

If you are getting a  table already exists  error  make changes in the SQL string as below   CREATE table IF NOT EXISTS table name  para1 para2     This way you can avoid the exceptions

User · Answer

The most reliable way I have found in C  right now  using the latest sqlite-net-pcl nuget package  1 5 231  which is using SQLite 3  is as follows   var result   database GetTableInfo tableName   if   result    null      result Count    0         database CreateTable lt T gt  CreateFlags AllImplicit

User · Answer

Note that to check whether a table exists in the TEMP database  you must use sqlite temp master instead of sqlite master   SELECT name FROM sqlite temp master WHERE type  table  AND name  table name

User · Answer

A variation would be to use SELECT COUNT    instead of SELECT NAME  i e  SELECT count    FROM sqlite master WHERE type  table  AND name  table name    This will return 0  if the table doesn t exist  1 if it does  This is probably useful in your programming since a numerical result is quicker   easier to process  The following illustrates how you would do this in Android using SQLiteDatabase  Cursor  rawQuery with parameters  boolean tableExists SQLiteDatabase db  String tableName        if  tableName    null    db    null     db isOpen                  return false            Cursor cursor   db rawQuery          quot SELECT COUNT    FROM sqlite master WHERE type     AND name     quot          new String     quot table quot   tableName             if   cursor moveToFirst                  cursor close            return false            int count   cursor getInt 0       cursor close        return count  gt  0

User · Answer

I thought I d put my 2 cents to this discussion  even if it s rather old one    This query returns scalar 1 if the table exists and 0 otherwise   select      case when exists           select 1 from sqlite master WHERE type  table  and name    your table            then 1          else 0      end as TableExists

User · Answer

If you re using SQLite version 3 3  you can easily create a table with      create table if not exists TableName  col1 typ1       colN typN    In the same way  you can remove a table only if it exists by using   drop table if exists TableName

User · Answer

SQLite table names are case insensitive  but comparison is case sensitive by default  To make this work properly in all cases you need to add COLLATE NOCASE   SELECT name FROM sqlite master WHERE type  table  AND name  table name  COLLATE NOCASE

User · Answer

Use  SELECT 1 FROM table LIMIT 1    to prevent all records from being read

User · Answer

If you re using fmdb  I think you can just import FMDatabaseAdditions and use the bool function    yourfmdbDatabase tableExists tableName

User · Answer

class CPhoenixDatabase        def   init   self  dbname           self dbname   dbname         self conn   sqlite3 connect dbname       def is table self  table name               This method seems to be working now            query    SELECT name from sqlite master WHERE type  table  AND name       table name                 cursor   self conn execute query          result   cursor fetchone           if result    None              return False         else              return True   Note  This is working now on my Mac with Python 3 7 1

User · Answer

Use this code   SELECT name FROM sqlite master WHERE type  table  AND name  yourTableName     If the returned array count is equal to 1 it means the table exists  Otherwise it does not exist

[sqlite] How do I check in SQLite whether a table exists?

Examples related to sqlite