Check if table exists and if it doesn't exist, create it in SQL Server 2008

Solution 1:

Something like this

IF  NOT EXISTS (SELECT * FROM sys.objects 
WHERE object_id = OBJECT_ID(N'[dbo].[YourTable]') AND type in (N'U'))

BEGIN
CREATE TABLE [dbo].[YourTable](
    ....
    ....
    ....
) 

END

Solution 2:

Just for contrast, I like using the object_id function as shown below. It's a bit easier to read, and you don't have to worry about sys.objects vs. sysobjects vs. sys.all_objects vs. sys.tables. Basic form:

IF object_id('MyTable') is not null
    PRINT 'Present!'
ELSE
    PRINT 'Not accounted for'

Of course this will show as "Present" if there is any object present with that name. If you want to check just tables, you'd need:

IF object_id('MyTable', 'U') is not null
    PRINT 'Present!'
ELSE
    PRINT 'Not accounted for'

It works for temp tables as well:

IF object_id('tempdb.dbo.#MyTable') is not null
    PRINT 'Present!'
ELSE
    PRINT 'Not accounted for'