CODEWITHSUNDEEP
sql-servert-sqlsql-server-2012
StackTrace
StackTrace

Reputation: 9416

Is this the right way to check if a table exists before creating it?

IF NOT EXISTS(SELECT * FROM sys.tables 
        WHERE [name] = N'MyTableName' AND [object_id] = OBJECT_ID(N'MyDataBaseName'))
BEGIN

CREATE TABLE [dbo].[MyTableName]
(
    [ID] [uniqueidentifier] NOT NULL,
    [FirstName] [nvarchar](300) NULL,
    [LastName] [nvarchar](300) NULL,
    [Index_No] [varchar](50) NULL   
) 
END

I am using the above T-SQL to check if a table exists in my database and if it does not exist i create. I am however wonder if this is the right way of doing it or is there better recomended approach?

Upvotes: 0

Views: 90

Answers (4)

SCK
SCK

Reputation: 1

You can also try like this:

IF NOT EXISTS (SELECT * FROM sys.objects WHERE name='MyTableName' AND type='U')
   BEGIN
       CREATE TABLE [dbo].[MyTableName]
       (
        [ID] [uniqueidentifier] NOT NULL,
        [FirstName] [nvarchar](300) NULL,
        [LastName] [nvarchar](300) NULL,
        [Index_No] [varchar](50) NULL  
       ) 
   END

Upvotes: 0

Pred
Pred

Reputation: 9042

There is a logical error in your query, namely the object_id column in sys.tables is not related to the database (it is the table's object_id from the sys.objects catalog view).

OBJECT_ID('DatabaseName') will evaulate to NULL unless there is a table (an object, not just a table) named exactly as the database itself, but in this case, you will retrieve that object's object_id instead of the database's.

Because of the above two reasons, your IF statement will always evaulates to true. (The query will not find any match, so the EXISTS will evaulate to false)

Almazini's answer is a pretty good approach. I recommend to use it.

Upvotes: 1

Stan
Stan

Reputation: 1999

If you have simple creation statements, then condition like

IF OBJECT_ID('dbo.TableName', 'U') IS NULL BEGIN
    ...statements....
END

will be good enough. If your script is more complex, there is another approach:

-- If object exists we disable scripts execution
IF OBJECT_ID('dbo.TableName', 'U') IS NOT NULL SET NOEXEC ON

...you can even create triggers/functions/procedures here...

-- Enable script execution
SET NOEXEC OFF

Upvotes: 0

Almazini
Almazini

Reputation: 1873

I am using a bit smaller query to do that:

IF OBJECT_ID('tempdb..##Product', 'U') IS  NULL 
CREATE TABLE ##Product (DataTypeID INT NULL, 
            ProductID INT  NOT NULL,
            CountryCodeID INT NOT NULL)
ELSE TRUNCATE TABLE ##Product;

Upvotes: 0

Related Questions