CODEWITHSUNDEEP
sql-serversyntax-error
masiboo
masiboo

Reputation: 4719

A very simple T-SQL statement syntax error

I'm just trying to query if any table exist in my database by the following statement:

IF EXISTS(SELECT 1 FROM db_name.sys.tables WHERE name = 'table_name')

I get a syntax error

Incorrect syntax near ')'

What is wrong? How to check if a table exists in my database.

Upvotes: 0

Views: 604

Answers (2)

S.Serpooshan
S.Serpooshan

Reputation: 8388

You should choose some output to check if the table exists. For example:

IF EXISTS(SELECT 1 FROM sys.tables WHERE name = 'table1') Select 1 ELSE Select 0

This will return 0 or 1 if table1 exists or not.

Other way:

SELECT 1 WHERE EXISTS(SELECT 1 FROM sys.tables WHERE name = 'table1')

Whcih will return 1 if table1 exists, or no record otherwise.

Note: You can also omit the EXISTS and use something like this:

SELECT 1 FROM sys.tables WHERE name = 'table1'

which will return 1 or no record based on existence of table1.

Upvotes: 0

StuartLC
StuartLC

Reputation: 107237

The IF condition in SQL needs a statement or block to be executed if the condition matches (i.e. the "then" statement or block), and optionally an "else" statement / block can be added, like so:

IF EXISTS(SELECT 1 FROM mydbname.sys.tables WHERE name = 'MyTable')
    PRINT 'FOUND';
ELSE
    PRINT 'NOT FOUND';

If you need to execute multiple statements in either 'then' or 'else', then wrap them in a block delimited with BEGIN / END.

Note if you are from a FP background, the IF statement is the old school side-effecting branch style, not the conditional / pattern match style, i.e. you can't do this

DECLARE @isFound BIT;
SET @isFound = IF EXISTS(SELECT 1 FROM mydbname.sys.tables WHERE name ='MyTable')
-- ** syntax error

Upvotes: 1

Related Questions