Check if Table Exists in SQL

With SQL we can use various methods to check whether or not a table (or other object) exists in the database. The method we use will often depend on the RDBMS we’re using, as well as the task we’re trying to undertake.

There’s usually a reason we’re trying to check for the existence of a table, and often the syntax we use will be tied to that reason. For example the ...IF EXISTS clause is a handy addition to the DROP TABLE statement, and the ...IF NOT EXISTS clause can often be used with the CREATE TABLE statement.

Other times we may simply want to see if the table exists without performing any immediate actions against that table. In such cases, we would need to run code specifically to see if the table exists.

Below are examples of code we can use in each of the above scenarios.

Check when Creating a Table

Many RDBMSs support the ...IF EXISTS/...IF NOT EXISTS syntax on commands like DROP TABLE and CREATE TABLE. This means we don’t need to write any other special code to check for the existence of the table – the syntax does that for us.

Here’s an example of using this syntax to create a table:

CREATE TABLE IF NOT EXISTS t1 (
  c1 INT,
  c2 VARCHAR(10)
);

This creates a table called t1 only if it doesn’t already exist in the database. The IF NOT EXISTS part tells the system to check for the existence of a table with that name. If such a table already exists, then the command is skipped and no new table is created, leaving the original table intact, and avoiding any errors that would have resulted from trying to create a table with the same name of an existing one.

The above statement may or may not work for you, depending on the RDBMS that you’re using. RDBMSs that support the CREATE TABLE IF NOT EXISTS syntax include MySQL, MariaDB, PostgreSQL, and SQLite.

The latest version of SQL Server at the time of writing (SQL Server 2022) doesn’t support the CREATE TABLE IF NOT EXISTS syntax. However, here are 2 Ways to Create a Table if it Doesn’t Exist in SQL Server.

Also, Oracle has only recently added this feature to its latest version Oracle Database 23c. If you’re using an earlier version, here are 2 ways to do it in Oracle prior to Oracle Database 23c.

When Dropping a Table

We can use a similar syntax to drop the table:

DROP TABLE IF EXISTS t1;

In this case, we use IF EXISTS instead of IF NOT EXISTS (i.e. we drop the NOT from our command).

RDBMSs that support the DROP TABLE IF EXISTS syntax include MySQL, MariaDB, PostgreSQL, SQLite, SQL Server, and Oracle (from Oracle Database 23c). Here are 2 alternatives for Oracle pre-23c.

Other Cases

Sometimes we might want to check for the existence of a table without performing another action. Or sometimes we may be using an RDBMS that doesn’t support the methods shown in the above examples, and so we need to write our own code that checks that the table exists before performing another action.

Here’s an example of code that should work in most RDBMSs:

SELECT * FROM information_schema.tables
WHERE table_schema = 'dbo' 
AND table_name = 't1';

You’d need to replace dbo and t1 with the actual schema name and table name respectively.

This code will return a row that contains information about the table. You can specify individual columns to return (instead of using the * wildcard) if you want. Either way, if the query doesn’t return any rows, then you know that such a table doesn’t exist.

If you don’t know the schema name, it’s possible to remove that from the query, but bear in mind this could return multiple tables across multiple schemas.

Here’s an example of doing that:

SELECT 
    table_schema,
    table_name,
    table_type,
    create_time
FROM information_schema.tables
WHERE table_name = 't1';

Result:

+--------------+------------+------------+---------------------+
| TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE | CREATE_TIME         |
+--------------+------------+------------+---------------------+
| krankykranes | t1         | BASE TABLE | 2023-10-24 10:27:44 |
| PetHotel     | t1         | BASE TABLE | 2022-02-17 12:34:44 |
| test         | t1         | BASE TABLE | 2023-12-23 10:21:33 |
+--------------+------------+------------+---------------------+
3 rows in set (0.00 sec)

In this case there are three tables of that name. Each of the tables is under a different schema. So we would need to know which is the relevant schema for our needs before determining whether or not the table exists in our schema.

Depending on your RDBMS, the schema could be the actual database name, or it could be a separate schema name that’s completely independent of the database name.

We can use the above query as a subquery in order to perform some other action that depends on the outcome of our subquery.

For example in SQL Server we can do this:

IF NOT EXISTS (
    SELECT * FROM information_schema.tables
    WHERE table_schema = 'dbo' AND table_name = 't1') 	
    CREATE TABLE dbo.t1 (
        c1 int, 
        c2 varchar(255)
        );

The above code creates a table called t1 only if it doesn’t already exist.

The actual syntax will depend on the RDBMS, but this concept can be useful for RDBMSs that don’t support the IF EXISTS/IF NOT EXISTS syntax of CREATE/DROP statements.

Other Methods

The information_schema is just one of multiple options for querying metadata. Many RDBMSs have various other tables and views that contain metadata about objects such as tables in the database. Many of these are outlined in the following articles: