ChatGPT解决这个技术问题 Extra ChatGPT

How do I check in SQLite whether a table exists?

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

k
kelalaka

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)


Which of the SQLite documentation covers these system tables?
@Pawel Veselov: The section titled "File Format For SQLite Databases": sqlite.org/fileformat2.html
This won't work for TEMP tables, however. TEMP tables are in "sqlite_temp_master."
Does this return a boolean? What does it return if the table does or doesn't exist?
@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).
E
Eric Platon

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

there is also similar construct for indexes: create index if not exists TableName_col1 on TableName(col1)
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, you might be right. Although the OP did not ask this, I was looking for this answer :)
S
Stephen Quan

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

I believe a "SELECT 1" would be even faster.
Why is cursor.getInt(0) equals to count of records in database?
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.
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.)
SELECT EXISTS(SELECT 1 FROM sqlite_master WHERE type="table" AND name="table_name") seems to really cut the result down to a boolean result (either 0 or 1, nothing else)
G
Galwegian

You could try:

SELECT name FROM sqlite_master WHERE name='table_name'

type = table would be useful tho
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
W
Wolf

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;

B
Brice M. Dempsey

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

C
Community

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

This is a great way to determine if a table exists in Python.
or Xamarin Forms
This is a great way to get at the column definitions programmatically
P
Peter Mortensen

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.


K
Kjuly

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

[yourfmdbDatabase tableExists:tableName].

Make sure you import "FMDatabaseAdditions.h" in order to use this method or else you'll wonder why they removed it! :)
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
P
Peter Mortensen

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"

This will still return nothing if the table doesn't exist, because the where condition prevents any result.
S
Scott Deerwester

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

D
DroidGrailer

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

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.
P
Peter Mortensen

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.


e
eyllanesc
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


This looks more cleaner than all other answers.. Thank You !!
Doesnt work for me: have to erase the {} brackets around table_name, then its fine.
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
m
mindus

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'

How is this different from the already accepted topvoted answer from 9 years ago?
F
Franz Fahrenkrog Petermann

Use

SELECT 1 FROM table LIMIT 1;

to prevent all records from being read.


This returns NULL if the table exists but does not have any records.
If the table does not exist, it will throw an error. Catch that, and you know it doesn't exist.
using error handling as flow control is generally not considered best practice. This should probably be avoided.
P
Peter Mortensen

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


e
eyllanesc

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

P
Piotr Rodak

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

NIce and neat (yes I commented on 2017 answer in 2022)
N
NoComprende

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

public static boolean tableExists(SQLiteDatabase database, String tableName){ return database.rawQuery("SELECT name FROM sqlite_master WHERE type='table' AND name='" + tableName + "'", null).moveToFirst(); }
Very inefficient and risky way as string concatenation may end up to everything.
S
Shivam Pokhriyal

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

Should probably close the resultSet before returning
s
sn0wbl1nd

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.


P
Peter Mortensen

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

S
Srinivasan_iOS

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

y
yuviscor

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.


S
StickySli

I wanted to add on Diego Vélez answer regarding the PRAGMA statement.

From https://sqlite.org/pragma.html we get some useful functions that can can return information about our database. Here I quote the following:

For example, information about the columns in an index can be read using the index_info pragma as follows: PRAGMA index_info('idx52'); Or, the same content can be read using: SELECT * FROM pragma_index_info('idx52'); The advantage of the table-valued function format is that the query can return just a subset of the PRAGMA columns, can include a WHERE clause, can use aggregate functions, and the table-valued function can be just one of several data sources in a join...

Diego's answer gave PRAGMA table_info(table_name) like an option, but this won't be of much use in your other queries.

So, to answer the OPs question and to improve Diegos answer, you can do

SELECT * FROM pragma_table_info('table_name');

or even better,

SELECT name FROM pragma_table_list('table_name');

if you want to mimic PoorLuzers top-voted answer.


M
Mohamed Reda

If you deal with Big Table, I made a simple hack with Python and Sqlite and you can make the similar idea with any other language

Step 1: Don't use (if not exists) in your create table command

you may know that this if you run this command that will have an exception if you already created the table before, and want to create it again, but this will lead us to the 2nd step.

Step 2: use try and except (or try and catch for other languages) to handle the last exception

here if you didn't create the table before, the try case will continue, but if you already did, you can put do your process at except case and you will know that you already created the table.

Here is the code:

def create_table():
    con = sqlite3.connect("lists.db")
    cur = con.cursor()
    try:
        cur.execute('''CREATE TABLE UNSELECTED(
        ID INTEGER PRIMARY KEY)''')
        print('the table is created Now')

    except sqlite3.OperationalError:
        print('you already created the table before')
    con.commit()
    cur.close()

L
Luis Tejeda

You can use a simple way, i use this method in C# and Xamarin,

public class LoginService : ILoginService
{
    private SQLiteConnection dbconn; 
}

in login service class, i have many methods for acces to the data in sqlite, i stored the data into a table, and the login page it only shows when the user is not logged in.

for this purpose I only need to know if the table exists, in this case if it exists it is because it has data

public int ExisteSesion()
    {
        var rs = dbconn.GetTableInfo("Sesion");
        return rs.Count;
    }

if the table does not exist, it only returns a 0, if the table exists it is because it has data and it returns the total number of rows it has.

In the model I have specified the name that the table must receive to ensure its correct operation.

    [Table("Sesion")]
public class Sesion
{
    [PrimaryKey]
    public int Id { get; set; }
    public string Token { get; set; }
    public string Usuario { get; set; }

}