In SQL, we can use the CREATE TABLE IF NOT EXISTS statement to create a table only if it doesn’t exist. The benefit of doing this is that we won’t get an error if there’s already a table with the same name.

But SQL Server doesn’t support this syntax – at least not in the current version of SQL Server at the time of writing (SQL Server 2022) .

So with SQL Server, we need to do a bit of extra work.

Example

Here’s a simple example:

IF NOT EXISTS (
    SELECT * FROM sys.tables t 
    JOIN sys.schemas s ON (t.schema_id = s.schema_id) 
    WHERE s.name = 'dbo' AND t.name = 't1') 	
    CREATE TABLE dbo.t1 (
        c1 int,
        c2 varchar(10)
        );

This example checks whether or not the table exists before trying to create it. If it doesn’t exist, then the table is created. Otherwise nothing happens.

The code still uses IF NOT EXISTS, but it’s in a different context to the CREATE TABLE IF NOT EXISTS syntax. In this case we start off with IF NOT EXISTS, followed by a subquery.

The subquery checks the sys.tables view for the existence of a table of the given name (t1) under the schema of the given name (dbo).

We could alternatively check the INFORMATION_SCHEMA.TABLES view, or other views/tables that provide metadata about tables.

Here it is using the INFORMATION_SCHEMA.TABLES view:

IF NOT EXISTS (
        SELECT * FROM INFORMATION_SCHEMA.TABLES
        WHERE TABLE_SCHEMA = 'dbo'
        AND TABLE_TYPE = 'BASE TABLE'
        AND TABLE_NAME = 't1'
    ) 	
    CREATE TABLE dbo.t1 (
        c1 int,
        c2 varchar(10)
        );

In this example I also checked the table type (i.e. TABLE_TYPE = 'BASE TABLE').

I can run either of the above statements and they will always succeed without error, whether the table exists or not:

Commands completed successfully.