SQL Server : check if table exists, otherwise create it

Question

I have this code I am converting from MySQL:

IF NOT EXISTS (SELECT * 
               FROM INFORMATION_SCHEMA.TABLES 
               WHERE TABLE_SCHEMA = 'TheSchema' 
                 AND TABLE_NAME = 'odds_soccer') 
    CREATE TABLE "odds_soccer" (...)

Only problem is... If I try run it twice, it will stump second time with error

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

Since I actually found my solution at SO, I am not sure why the code is not working.

I am currently running it as a query from Microsoft SQL Server Management Studio having selected my database.

Answer 1

For creating table

IF NOT EXISTS (SELECT 'X'
                   FROM   INFORMATION_SCHEMA.TABLES
                   WHERE  TABLE_NAME = 'table_name'
                          AND TABLE_SCHEMA = 'schema')
BEGIN
    create..
END

For dropping existing table create new table

IF Object_id('TEMPDB.schema_name.table', 'U') IS NOT NULL
    DROP TABLE table;