T-SQL doesn’t include the IF NOT EXISTS clause with its CREATE TABLE statement, like some other DBMSs do.

Therefore, if we want to check for the existence of the table before we create it in SQL Server, we need to use other methods.

Option 1: Check the Object ID

In SQL Server, we can use the OBJECT_ID() function to check for the existence of the table before we try to create it:

IF OBJECT_ID(N'dbo.t1', N'U') IS NULL
CREATE TABLE dbo.t1 (
    c1 int,
    c2 varchar(10)
    );
GO

The above example checks the object ID of a dbo.t1 table.

The second argument to OBJECT_ID() specifies the type of object we’re looking for. In this case we use U, which is for “user defined table”.

The OBJECT_ID() function returns the database object identification number of a schema-scoped object. If the object doesn’t exist, or if you don’t have access to it, the function returns NULL. Therefore, we can check for a NULL value, and only create the table if this function returns NULL.

We can use the OBJECT_ID() function to check the table’s object ID once it’s been created:

SELECT OBJECT_ID(N'dbo.t1', N'U');

Example result:

354100302

If we wanted a bit more info, we could do something like this:

sp_tables 't1';

Result:

+-------------------+---------------+--------------+--------------+-----------+
| TABLE_QUALIFIER   | TABLE_OWNER   | TABLE_NAME   | TABLE_TYPE   | REMARKS   |
|-------------------+---------------+--------------+--------------+-----------|
| KrankyKranes      | dbo           | t1           | TABLE        | NULL      |
+-------------------+---------------+--------------+--------------+-----------+

Here, KrankyKranes is the database that I created the table in.

There are many other ways to look at existing tables. See 6 Ways to Check if a Table Exists in SQL Server for examples.

Option 2: Query sys.tables

Another way to check whether a table already exists is to query the sys.tables system catalog view.

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

That does a similar thing to the previous example; it checks for the existence of the table and creates it only if it doesn’t exist.

Regardless of the method used to check for the table’s existence, the command completes successfully, regardless of whether the table exists or not.

So we get the following output from both methods:

Commands completed successfully.

I get that message whether the table already existed or not.

If the table already exists, but we don’t check for the existence of the table, we get an error like this:

Msg 2714, Level 16, State 6, Line 1
There is already an object named 't1' in the database.

It’s important to note that, just because a table of that name already exists in the database, it doesn’t meant that it has the correct definition. The above methods simply check for the table by name and schema.