Drop temp table if it exists

Question

Friends,

I am creating a temp table. The script may be run several times so I need to check if the temp table exist then drop it. I have the written the code below but I get an error when running the script twice, that the table already exists:

There is already an object named '#lu_sensor_name_19' in the database.

It appears that IF OBJECT_ID('alarm..#lu_sensor_name_19') IS NOT NULL does not return true when the tablle is not null. What am I doing wrong?

IF OBJECT_ID('alarm..#lu_sensor_name_19') IS NOT NULL 
BEGIN 
    DROP TABLE #lu_sensor_name_19 
END

CREATE TABLE #lu_sensor_name_19(
    sensorname_id int NOT NULL,
    sensorname nvarchar(50) NOT NULL,
    paneltype_id smallint NOT NULL,
    panel_version_id int NULL,
    prefix_allowed tinyint NOT NULL,
    base_allowed tinyint NOT NULL,
    suffix_allowed tinyint NOT NULL,
    key_value int NULL,
    sort_index int NULL,
    device_allowed tinyint NOT NULL,
    sensor_name_group_id smallint NOT NULL,
    )

Answer 1

Temp #Tables are created in tempdb. Try this:

IF OBJECT_ID('tempdb..#lu_sensor_name_19') IS NOT NULL 
BEGIN 
    DROP TABLE #lu_sensor_name_19 
END

CREATE TABLE #lu_sensor_name_19...

SQL Server 2016 added the ability to do the drop in one line:

DROP TABLE IF EXISTS #lu_sensor_name_19 

CREATE TABLE #lu_sensor_name_19...