0

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

Doe Jowns
  • 184
  • 1
  • 3
  • 12
  • `and t.name = my_table` appears to be missing quotes. Other than that, I can't replicate your problem: this statement will not attempt to create a table that already exists. `IF (OBJECT_ID('dbo.mytable', 'U') IS NULL)` is a shorter alternative. Are you perhaps trying to execute this statement in the wrong database, or under an account that has no permissions to do anything with the table? – Jeroen Mostert Aug 11 '17 at 11:29
  • You also need to create a table as CREATE TABLE my_table... – Keith Aug 11 '17 at 11:31
  • @Leonidas199x I didn't copy this in the post because this part seems to work but the rest of the query exists x) – Doe Jowns Aug 11 '17 at 11:33
  • @JeroenMostert Normally I've got all the permissions.. I didn't implemented any users for the moment ^^ ! – Doe Jowns Aug 11 '17 at 11:34
  • But ok I will detail a bit more :) – Doe Jowns Aug 11 '17 at 11:35
  • 1
    Are you executing this in the database `TCPDUMP` (`USE TCPDUMP`)? If not, the check will fail since the system views are scoped to databases. – Jeroen Mostert Aug 11 '17 at 11:46
  • Ok there was an error in the string connection.. I had the wrong database. Sorry for disturbing, I really thought it was in the SQL query.. – Doe Jowns Aug 11 '17 at 11:50

2 Answers2

1

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
Dan Guzman
  • 43,250
  • 3
  • 46
  • 71
0

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?

Keith
  • 1,008
  • 10
  • 19