If table exists with dynamic query in SQL Server

Question

I'm a little confused on the code that is being suggested for checking if a table exists. Can someone explain whether the code that I have written will work for checking if a table exists?

I want it to do nothing if the table does exist.

Here is my code:

BEGIN 
SET NOCOUNT ON;

DECLARE @SQL NVARCHAR(MAX);

IF NOT EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N' + @TABLENAME + ') AND type in (N'U'))

    BEGIN
        SET @SQL = 

        N'CREATE TABLE ' + @TABLENAME + '
        ('
        + '[ID] [int] IDENTITY(1,1) NOT NULL,
        [intID] [int] NULL,
        [varID] [varchar](50) NULL,
        [FormName] [varchar](250) NULL,
        [UID] [varchar](3) NOT NULL,
        CONSTRAINT [PK_Selections' + @TABLENAME + '_1] PRIMARY KEY CLUSTERED(
        [ID]));';

        EXEC sp_executesql @sql;
    END


END

I forgot to mention that currently the procedure DOES create the table. But when I run the procedure again it tries to create the table again and then fails.

How do I make it exit the procedure if the table exists?

gbn · Accepted Answer · 2014-08-07 09:59:38Z

7

Just do this simple check. No need to query sys.objects

...    
IF OBJECT_ID(@TABLENAME, 'U') IS NULL
BEGIN

Your check failed because you were actually looking for a table called "+ @TABLENAME +"

answered Aug 7, 2014 at 9:59

gbn

435k85 gold badges602 silver badges690 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Justin Over a year ago

Can you briefly explain why I wouldn't use the other method just so I understand further please

MikkaRin · Accepted Answer · 2014-08-07 10:05:04Z

1

Here is the checking whether table exists or not:

IF NOT EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N' + @TABLENAME + ') AND type in (N'U'))

table sys.objects contains description all objects in a database.

Function OBJECT_ID() - return id of object by its name.

type in (N'U')) - checks that object was created by user.

To check that table is EXISTS use check:

IF EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N' + @TABLENAME + ') AND type in (N'U'))
BEGIN
...
END

edited Aug 7, 2014 at 10:05

answered Aug 7, 2014 at 9:59

MikkaRin

3,08422 silver badges35 bronze badges

1 Comment

gbn Over a year ago

The code is wrong, no matter how you explain it... The question mentions this at the end when it keeps trying to create the table

Pawan · Accepted Answer · 2014-08-07 10:20:31Z

1

Try this:

IF NOT EXISTS(SELECT TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE
 TABLE_NAME=LTRIM(RTRIM(@TABLENAME )) AND TABLE_TYPE='BASE TABLE'

BEGIN
  -- CREATE YOUR TABLE
END

edited Aug 7, 2014 at 10:20

answered Aug 7, 2014 at 10:14

Pawan

1,7421 gold badge16 silver badges37 bronze badges

Collectives™ on Stack Overflow

If table exists with dynamic query in SQL Server

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related