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?

SQLite will throw an exception if the table in a select does not exist. There is simply no need for any more fancy work. — NoChance, Oct 03 '16 at 18:22
@NoChance it will, but so will any number of other things. That's a bit like seeing if that tree is really there by driving forwards with your eyes shut, you'll find out one way or the other :) — randomsock, Feb 11 '17 at 20:01
@randomsock, I don't know what's the sqlite convention, but it's more pythonic to ask for forgiveness than permission. i.e. catch the exception instead of using a conditional. — Eric, Oct 18 '18 at 15:42
@Eric As of now, the question doesn't involve Python, but assuming it did, the error is a generic `sqlite3.OperationalError`, so you have to parse the error message in order to make sure it's e.g. "table TABLE_NAME already exists" message when you create a table, and if not, reraise the error and I think there's no guarantee the phrasing of the error won't change. — Markus von Broady, Sep 17 '19 at 19:23

score 1093 · Accepted Answer · edited Oct 07 '19 at 09:53

1093

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)

edited Oct 07 '19 at 09:53

kelalaka

4,046
4
22
39

answered Oct 21 '09 at 22:57

PoorLuzer

21,986
7
26
33

8

Which of the SQLite documentation covers these system tables? – Pawel Veselov Jan 03 '12 at 04:59
33

@Pawel Veselov: The section titled "File Format For SQLite Databases": http://www.sqlite.org/fileformat2.html – Bryan Oakley Jan 11 '12 at 16:34
1

This doesn't work if you are using Adobe Air SQLite, here is a (better) alternative for the case that you are programming in AS3/Air: http://stackoverflow.com/questions/4601707/air-and-sqlite-if-table-exists-conditional – CenterOrbit Jul 09 '12 at 03:08
17

This won't work for TEMP tables, however. TEMP tables are in "sqlite_temp_master." – PatchyFog Dec 18 '12 at 16:04
how to write the same query for web-sql – M.S.Naidu Aug 11 '14 at 14:18
I am using ZF2 with sqlite, when used this query, zf2 doesn't allow me to go further, i.e. 'sqlite_master' is not giving me the expected output. So I used the below solution but works smoothly :) – Rohutech May 07 '15 at 15:52
14

Does this return a boolean? What does it return if the table does or doesn't exist? – Dagrooms Jun 15 '15 at 18:24
10

@Dagrooms 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). – Rein S Dec 01 '15 at 19:42
5

@PoorLuzer can you please update your answer how to check result of query to decide table exists or not? It will save time of newbie – Shirish Herwade Jan 22 '16 at 12:11
@ReinS I run this query and check if(return > 0) but return always evaluates to -1 when I run the statement. What would give a -1? – Alex Watts Feb 08 '17 at 17:54
@AlexWatts That really depends on what language you're using these SQL statements within. For example, an Android `Cursor`'s method of `getCount()` shouldn't really ever return a -1 (more likely an exception would be thrown on or before that if something was wrong). – Rein S Feb 08 '17 at 19:17
@ShirishHerwade I have edited the above answer to include the example you requested. I hope it's clear enough. – mtotowamkwe Sep 02 '19 at 02:43
pretty certain temp tables are not in this list. – Richard Jan 14 '20 at 13:38
and just because I add to find how to do : if you are want to check if a table exists in an attached database, you simply query on the sqlite_master of the attached database . `SELECT name FROM myattacheddb.sqlite_master WHERE type='table' AND name='{table_name}';` – Tuckbros Feb 12 '21 at 10:43

score 600 · Answer 2 · edited May 31 '13 at 02:50

600

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

edited May 31 '13 at 02:50

Eric Platon

8,821
6
37
45

answered Mar 05 '11 at 17:36

arthur johnston

6,136
1
13
4

13

there is also similar construct for indexes: **create index if not exists TableName_col1 on TableName(col1)** – lowtech Dec 02 '13 at 14:34
31

This should not be the accepted answer, but would if the question were worded differently. The OP didn't ask how to check a table before dropping or creating. What if you have to query a table that possibly doesn't exist? This is the problem I'm facing now, and the accepted answer works best in this general problem statement. This is a good quick alternative. – Dagrooms Jun 16 '15 at 18:15
1

@Dagrooms, you might be right. Although the OP did not ask this, I was looking for this answer :) – Enis Arik May 17 '20 at 12:12

Stephen Quan · Answer 3 · 2020-10-13T22:21:41.367

189

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(
       "SELECT COUNT(*) FROM sqlite_master WHERE type = ? AND name = ?",
       new String[] {"table", tableName}
    );
    if (!cursor.moveToFirst())
    {
        cursor.close();
        return false;
    }
    int count = cursor.getInt(0);
    cursor.close();
    return count > 0;
}

edited Oct 13 '20 at 22:21

answered Jan 11 '12 at 22:20

Stephen Quan

15,118
3
69
63

37

I believe a "SELECT 1" would be even faster. – PatchyFog Dec 18 '12 at 15:49
Why is cursor.getInt(0) equals to count of records in database? – Semyon Danilov Jan 23 '14 at 18:57
1

We're counting the number of times the TABLE appears in sqlite schema. A count of 0 means the table doesn't exist. A count of 1 means the table does exist. These are the only two expected values of count. – Stephen Quan Jan 23 '14 at 23:55
1

While the number (from `COUNT(*)`) is easy to process, it's even easier to return the existence of a row or not; if there's a row there then it exists, if there's no row it doesn't. (You already check for failure in moveToFirst, so the work would be done at that point.) – dash-tom-bang Jul 26 '16 at 17:05
Please update your code to close the cursor before you return false. – Dave Thomas Sep 06 '16 at 18:42
On second thought can that return false ever happen? Seems highly unlikely. – Dave Thomas Sep 06 '16 at 18:47

score 50 · Answer 4 · answered Oct 21 '09 at 14:25

50

You could try:

SELECT name FROM sqlite_master WHERE name='table_name'

answered Oct 21 '09 at 14:25

Galwegian

40,271
15
106
157

5

type = table would be useful tho – mafu Mar 30 '12 at 13:02
If using C#, don't use this command in a `SQLiteReader reader = cmd.ExecuteReader();` and do a `dt.Load(reader)` (where `dt` is a `DataTable`). I found it gives this `Object reference is not an instance of an object` exception on the `.Load()` if the table isn't found. Instead, use a `SQLiteDataAdapter adapter = new SQLiteDataAdapter(cmd); ` and do `adapter.Fill(ds)`, where `ds` is a `DataSet`. You can then see if `ds.Tables.Count > 0` and `return ds.Tables[0];` if so (or `else return null`). Then you can check that `DataTable` for being `null`, if `dt.Rows != null`, and if `dt.Rows.Count>0` – vapcguy Nov 16 '16 at 22:58

score 38 · Answer 5 · edited May 17 '21 at 10:53

38

See (7) How do I list all tables/indices contained in an SQLite database in the SQLite FAQ:

SELECT name FROM sqlite_master
WHERE type='table'
ORDER BY name;

edited May 17 '21 at 10:53

Wolf

8,482
7
48
92

answered Oct 21 '09 at 14:25

Anton Gogolev

107,051
37
191
278

score 37 · Answer 6 · edited Jun 20 '20 at 09:12

37

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 "pk" 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

edited Jun 20 '20 at 09:12

Community

1
1

answered Sep 29 '14 at 18:06

Diego Vélez

499
5
4

This is a great way to determine if a table exists in Python. – Michael Murphy Dec 09 '14 at 07:01
or Xamarin Forms – SerenityNow Aug 16 '17 at 20:17
5

This is a great way to get at the column definitions programmatically – w00t Nov 01 '17 at 15:47

score 35 · Answer 7 · edited Mar 03 '17 at 17:05

35

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.

edited Mar 03 '17 at 17:05

Peter Mortensen

28,342
21
95
123

answered Oct 20 '15 at 11:46

Rakesh Chaudhari

2,420
1
21
22

Brice M. Dempsey · Answer 8 · 2014-02-22T18:07:00.577

34

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

edited Feb 22 '14 at 18:07

answered Feb 21 '14 at 20:26

Brice M. Dempsey

1,770
18
13

score 24 · Answer 9 · edited Oct 26 '15 at 12:10

24

If you're using fmdb, I think you can just import FMDatabaseAdditions and use the bool function:

[yourfmdbDatabase tableExists:tableName].

edited Oct 26 '15 at 12:10

Kjuly

32,573
22
98
112

answered Apr 06 '12 at 04:46

user655489

1,206
3
12
19

1

Make sure you import "FMDatabaseAdditions.h" in order to use this method or else you'll wonder why they removed it! :) – Will Mar 17 '15 at 16:34
Although this could be a correct answer, the question was about sqlite not a particular library in a particular language. I think the answer should be to provide sql code, not a call to one of the methods of the library – nacho4d Apr 05 '16 at 01:39

score 13 · Answer 10 · edited Mar 03 '17 at 16:59

13

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"

edited Mar 03 '17 at 16:59

Peter Mortensen

28,342
21
95
123

answered Aug 08 '11 at 13:50

pacheco

139
1
2

1

This will still return nothing if the table doesn't exist, because the where condition prevents any result. – David Gausmann Apr 24 '18 at 17:47

score 11 · Answer 11 · answered Apr 24 '14 at 15:49

11

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';

answered Apr 24 '14 at 15:49

Scott Deerwester

2,452
2
24
43

score 9 · Answer 12 · answered Jun 11 '12 at 17:56

9

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;
    }
}

answered Jun 11 '12 at 17:56

DroidGrailer

99
1
2

1

I sadly had to use this in my Android app as I found that Samsung devices don't use the standard sqlite_master table structure that everyone else is working with. – Anthony Chuinard Jun 14 '15 at 05:18

score 7 · Answer 13 · edited Mar 03 '17 at 17:01

7

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.

edited Mar 03 '17 at 17:01

Peter Mortensen

28,342
21
95
123

answered Dec 20 '13 at 09:04

asmad

387
4
13

score 6 · Answer 14 · edited Jun 01 '19 at 07:54

6

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

edited Jun 01 '19 at 07:54

eyllanesc

190,383
15
87
142

answered Nov 14 '18 at 20:23

Douglas Goodall

111
1
5

This looks more cleaner than all other answers.. Thank You !! – hackwithharsha Jul 04 '19 at 15:53
Doesnt work for me: have to erase the {} brackets around table_name, then its fine. – Banana Aug 04 '19 at 12:09
1

Make sure `table_name` is not provided from untrused source (like user input), otherwise it will be vulnerable to SQL injection. It is always better to use parameters instead of text manipulation techniques – astef Aug 27 '19 at 16:59

score 4 · Answer 15 · answered Aug 02 '18 at 10:14

4

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'

answered Aug 02 '18 at 10:14

mindus

331
1
4
17

7

How is this different from the already accepted topvoted answer from 9 years ago? – Kevin Van Dyck Aug 02 '18 at 10:21

score 3 · Answer 16 · answered Oct 28 '14 at 17:27

3

Use

SELECT 1 FROM table LIMIT 1;

to prevent all records from being read.

answered Oct 28 '14 at 17:27

Franz Fahrenkrog Petermann

159
1
2

This returns NULL if the table exists but does not have any records. – radiospiel Jan 14 '15 at 12:38
If the table does not exist, it will throw an error. Catch that, and you know it doesn't exist. – luckydonald Jul 01 '15 at 17:15
using error handling as flow control is generally not considered best practice. This should probably be avoided. – Jeff Woodard Oct 03 '16 at 20:56

score 3 · Answer 17 · edited Jun 01 '19 at 07:54

3

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<T>(CreateFlags.AllImplicit);
}

edited Jun 01 '19 at 07:54

eyllanesc

190,383
15
87
142

answered Jan 18 '19 at 16:11

Matthew Joughin

101
1
4

score 2 · Answer 18 · edited Mar 03 '17 at 17:00

2

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).

edited Mar 03 '17 at 17:00

Peter Mortensen

28,342
21
95
123

answered Apr 11 '12 at 09:44

Grz

29
2

NoComprende · Answer 19 · 2020-01-20T07:22:25.110

1

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, &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;}

edited Jan 20 '20 at 07:22

answered Oct 23 '19 at 15:47

NoComprende

573
2
14

public static boolean tableExists(SQLiteDatabase database, String tableName){ return database.rawQuery("SELECT name FROM sqlite_master WHERE type='table' AND name='" + tableName + "'", null).moveToFirst(); } – nick Nov 16 '19 at 21:11
Very inefficient and risky way as string concatenation may end up to everything. – Andrea Moro May 06 '20 at 09:26

score 1 · Answer 20 · answered Mar 18 '21 at 06:10

1

You can also use db metadata to check if the table exists.

DatabaseMetaData md = connection.getMetaData();
ResultSet resultSet = md.getTables(null, null, tableName, null);
if (resultSet.next()) {
    return true;
}

answered Mar 18 '21 at 06:10

Shivam Pokhriyal

522
6
13

Should probably close the `resultSet` before returning – birgersp May 05 '21 at 08:55

score 0 · Answer 21 · edited Mar 03 '17 at 17:07

This is my code for SQLite Cordova:

get_columnNames('LastUpdate', function (data) {
    if (data.length > 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>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);
        });
    });
}

score 0 · Answer 22 · answered Jun 10 '17 at 22:13

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

score 0 · Answer 23 · answered Jun 01 '19 at 07:51

Table exists or not in database in swift

func tableExists(_ tableName:String) -> Bool {
        sqlStatement = "SELECT name FROM sqlite_master WHERE type='table' AND name='\(tableName)'"
        if sqlite3_prepare_v2(database, sqlStatement,-1, &compiledStatement, nil) == SQLITE_OK {
            if sqlite3_step(compiledStatement) == SQLITE_ROW {
                return true
            }
            else {
                return false
            }
        }
        else {
            return false
        }
            sqlite3_finalize(compiledStatement)
    }

score 0 · Answer 24 · answered Mar 20 '21 at 23:58

0

My preferred approach:

SELECT "name" FROM pragma_table_info("table_name") LIMIT 1;

If you get a row result, the table exists. This is better (for me) then checking with sqlite_master, as it will also check attached and temp databases.

answered Mar 20 '21 at 23:58

sn0wbl1nd

26
1
4

score 0 · Answer 25 · answered Apr 07 '21 at 20:53

If you are running it with the python file and using sqlite3 obviously. Open command prompt or bash whatever you are using use

python3 file_name.py first in which your sql code is written.
Then Run sqlite3 file_name.db.
.table this command will give tables if they exist.

How do I check in SQLite whether a table exists?

25 Answers25

Linked

Related