Introduction

The usual way to check whether a table exists in SQLite is to query the schema table. In older examples you will often see sqlite_master; in newer documentation you may also see sqlite_schema. Either way, the idea is the same: ask SQLite's catalog whether a table with that name is present.

In many cases, though, the best answer is not to check at all. If your real goal is to create the table safely, CREATE TABLE IF NOT EXISTS is simpler and more reliable than an explicit existence check.

Query the Schema Table

This is the standard existence check:

1SELECT name
2FROM sqlite_master
3WHERE type = 'table'
4  AND name = 'users';

If the query returns a row, the table exists. If it returns no rows, it does not.

If you prefer a boolean-style result:

1SELECT EXISTS (
2    SELECT 1
3    FROM sqlite_master
4    WHERE type = 'table'
5      AND name = 'users'
6);

That returns 1 for true and 0 for false.

Prefer Parameters in Application Code

If the table name comes from code, do not build raw SQL carelessly. In Python with sqlite3, an existence check looks like this:

1import sqlite3
2
3conn = sqlite3.connect(":memory:")
4cur = conn.cursor()
5cur.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)")
6
7table_name = "users"
8cur.execute(
9    """
10    SELECT EXISTS (
11        SELECT 1
12        FROM sqlite_master
13        WHERE type = 'table' AND name = ?
14    )
15    """,
16    (table_name,),
17)
18
19exists = cur.fetchone()[0] == 1
20print(exists)

That is a clean, explicit existence check for application logic.

PRAGMA table_info Can Be a Secondary Check

You will also see:

PRAGMA table_info(users);

If the table exists, SQLite returns its columns. If the result is empty, the table may not exist.

This is useful when you also need column metadata, but it is less direct than querying the schema table when existence is the only question.

If Your Goal Is Creation, Skip the Check

Many existence checks are only there because the code wants to create the table if missing. In SQLite, that is built in:

1CREATE TABLE IF NOT EXISTS users (
2    id INTEGER PRIMARY KEY,
3    name TEXT NOT NULL
4);

This is usually better than:

1. check whether the table exists
2. create it if missing

The one-step form avoids race conditions and makes the code simpler.

Distinguish Tables From Indexes and Views

The schema table contains more than tables. That is why the query filters with type = 'table'.

If you need to check for a view or an index instead, change the type:

1SELECT name
2FROM sqlite_master
3WHERE type = 'view'
4  AND name = 'active_users';

Without the type filter, you can get misleading matches.

Temporary Tables

Temporary tables live in a different schema area. If you specifically need a temp table, check sqlite_temp_master:

1SELECT name
2FROM sqlite_temp_master
3WHERE type = 'table'
4  AND name = 'temp_users';

This matters in scripts or tests that use temp tables heavily.

Common Pitfalls

The most common mistake is checking first when CREATE TABLE IF NOT EXISTS would solve the real problem more cleanly.

Another mistake is forgetting the type = 'table' filter and then matching a view or index with the same name pattern.

People also use PRAGMA table_info(...) as a generic existence test without realizing it is really a column-metadata command.

Finally, remember that temp tables and normal tables are stored in different schema catalogs. Checking the wrong one can make a table look missing when it is not.

Summary

• Query sqlite_master or sqlite_schema to check whether a normal table exists.
• Use SELECT EXISTS(...) if you want a boolean-style result.
• Prefer parameterized queries in application code.
• If your real goal is safe creation, use CREATE TABLE IF NOT EXISTS.
• Use sqlite_temp_master for temporary tables.