CODEWITHSUNDEEP
sql-server
Doe Jowns
Doe Jowns

Reputation: 302

My SQL server query doesn't recognize if a table exists

In relation with this question : I'm trying to use the following query

IF NOT EXISTS(
    SELECT T.name FROM SYS.TABLES T JOIN SYS.SCHEMAS S ON (T.SCHEMA_ID = S.SCHEMA_ID) 
    WHERE S.NAME = 'DBO' AND T.NAME = 'sample2Prot'
    ) 
BEGIN CREATE TABLE[TCPDUMP].[dbo].[sample2Prot] 
    (
    [IdTransmission] INT IDENTITY(1, 1) NOT NULL,
    [timestp]  NVARCHAR(32) NULL,
    [idq]  NVARCHAR(32) NULL,
    [idz]  NVARCHAR(32) NULL,
    [prot]  NVARCHAR(32) NULL,
    [Lhowmany]  NVARCHAR(32) NULL,
    [Rhowmany]  NVARCHAR(32) NULL,
    CONSTRAINT[PK_TCPDump] PRIMARY KEY CLUSTERED([IdTransmission] ASC)
    ) 
END;

The CREATE my_table.." part works, but if the table already exists, the program raises an error during the execution : it seems that the "if not exists" statement does not work. Anyone has an explanation ?

Feel free to ask for more details

Teh error <3

Upvotes: 0

Views: 346

Answers (2)

Dan Guzman
Dan Guzman

Reputation: 46233

The sample script you provided isn't valid syntax so we can only guess as to why your actual script doesn't work as expected, assuming the actual syntax is valid. I would expect that method to work but can be refactored as below:

IF OBJECT_ID(N'dbo.my_table', 'U') IS NULL
BEGIN
    CREATE TABLE dbo,my_table(col1 int);
END;

EDIT: The actual script in your revised question shows the table name is qualified with the database name but the system catalog views are not. Consequently, the current database is checked for object existence and you'll get an error if the script is run from a different database context and the table already exists. The revised script would be:

IF NOT EXISTS(
    SELECT T.name FROM TCPDUMP.sys.tables T JOIN TCPDUMP.sys.schemas S ON (T.SCHEMA_ID = S.SCHEMA_ID) 
    WHERE S.NAME = 'dbo' AND T.NAME = 'sample2Prot'
    ) 
BEGIN CREATE TABLE[TCPDUMP].[dbo].[sample2Prot] 
    (
    [IdTransmission] INT IDENTITY(1, 1) NOT NULL,
    [timestp]  NVARCHAR(32) NULL,
    [idq]  NVARCHAR(32) NULL,
    [idz]  NVARCHAR(32) NULL,
    [prot]  NVARCHAR(32) NULL,
    [Lhowmany]  NVARCHAR(32) NULL,
    [Rhowmany]  NVARCHAR(32) NULL,
    CONSTRAINT[PK_TCPDump] PRIMARY KEY CLUSTERED([IdTransmission] ASC)
    ) 
END;

Also, note the lower case for the system catalog views and the schema name in this example. This will ensure the script succeeds on a database with a case-sensitive collation.

You can also use the OBJECT_ID function with 3-part names:

IF OBJECT_ID(N'TCPDUMP.dbo.my_table', 'U') IS NULL

Upvotes: 1

Keith
Keith

Reputation: 1038

Cannot replicate with this code:

IF NOT EXISTS(SELECT T.name FROM SYS.TABLES T JOIN SYS.SCHEMAS S ON (T.SCHEMA_ID = S.SCHEMA_ID) WHERE S.NAME = 'DBO' AND T.NAME = 'MY_TABLE') 

BEGIN
    PRINT 'Creating table'
    CREATE TABLE MY_TABLE
    (
        ID INT 
    )
END

If that does not work, can you advise what version you are running and the exact messages that are presented?

Upvotes: 0

Related Questions