How to Check if a Table Exists in SQL

Posted by Kyle Hankinson February 15, 2024

---

Writing idempotent SQL scripts — ones that can run multiple times without errors — requires checking if tables exist before creating or dropping them.

IF NOT EXISTS / IF EXISTS

MySQL

CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

DROP TABLE IF EXISTS temp_data;

PostgreSQL

CREATE TABLE IF NOT EXISTS users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

DROP TABLE IF EXISTS temp_data;

SQLite

CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL
);

DROP TABLE IF EXISTS temp_data;

SQL Server (2016+)

-- SQL Server 2016+ supports DROP IF EXISTS
DROP TABLE IF EXISTS temp_data;

-- CREATE IF NOT EXISTS is NOT supported — use a check instead
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'users')
BEGIN
    CREATE TABLE users (
        id INT IDENTITY(1,1) PRIMARY KEY,
        name VARCHAR(100) NOT NULL
    );
END;

SQL Server (older versions)

IF OBJECT_ID('dbo.temp_data', 'U') IS NOT NULL
    DROP TABLE dbo.temp_data;

'U' means user table. Use 'V' for views, 'P' for procedures.

Oracle

Oracle does not have IF NOT EXISTS syntax. Use PL/SQL exception handling:

BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE temp_data';
EXCEPTION
    WHEN OTHERS THEN
        IF SQLCODE != -942 THEN RAISE; END IF;  -- -942 = table does not exist
END;

Or check the data dictionary first:

DECLARE
    v_count NUMBER;
BEGIN
    SELECT COUNT(*) INTO v_count FROM user_tables WHERE table_name = 'USERS';
    IF v_count = 0 THEN
        EXECUTE IMMEDIATE 'CREATE TABLE users (id NUMBER PRIMARY KEY, name VARCHAR2(100))';
    END IF;
END;

Checking If a Table Exists in a Query

MySQL

SELECT COUNT(*)
FROM information_schema.tables
WHERE table_schema = DATABASE()
    AND table_name = 'users';

PostgreSQL

SELECT EXISTS (
    SELECT FROM pg_tables
    WHERE schemaname = 'public'
        AND tablename = 'users'
);

SQL Server

SELECT CASE
    WHEN OBJECT_ID('dbo.users', 'U') IS NOT NULL THEN 1
    ELSE 0
END AS table_exists;

Quick Reference

Database	CREATE IF NOT EXISTS	DROP IF EXISTS
MySQL	Yes	Yes
PostgreSQL	Yes	Yes
SQLite	Yes	Yes
SQL Server 2016+	No (use IF NOT EXISTS check)	Yes
SQL Server < 2016	No (use OBJECT_ID check)	No (use OBJECT_ID check)
Oracle	No (use PL/SQL)	No (use PL/SQL)

Checking for Columns

Before adding a column, check if it already exists:

-- MySQL / PostgreSQL
SELECT COUNT(*) FROM information_schema.columns
WHERE table_name = 'users' AND column_name = 'email';

-- SQL Server
IF COL_LENGTH('dbo.users', 'email') IS NULL
    ALTER TABLE dbo.users ADD email VARCHAR(255);

Using SQLPro Studio

SQLPro for MSSQL's sidebar displays all tables in your database at a glance. After running migration scripts, use the refresh button to verify that new tables were created or dropped tables are gone — without writing any catalog queries.

---