OHDSI Home | Forums | Wiki | Github

Preferred syntax for checking if tables exist

Developers
#1

When conditionally dropping non-temp tables, the typical syntax is this (with a single period between schema and table):

IF OBJECT_ID('@cohort_database_schema.@cohort_inclusion_table', 'U') IS NOT NULL
	DROP TABLE @cohort_database_schema.@cohort_inclusion_table;

Similarly, when creating tables that do not exist, the typical syntax uses a single period:

IF OBJECT_ID('@results_schema.ir_strata', 'U') IS NULL
    CREATE TABLE @results_schema.ir_strata( ...

However, for the majority of the queries that drop temp tables, two periods are used after tempdb:

IF OBJECT_ID('tempdb..#cov_ref', 'U') IS NOT NULL
	DROP TABLE #cov_ref;

What is the preferred syntax? Do both single and double periods syntax work on all databases?

#2

I think the .. is an artifact of SqlRender and MS Sql (as the origin dialect). The tempdb schema is tempdb on MSSQL and for some reason the OBJECT_ID() wants a schema (it appears).

#3

To make life easier, SqlRender for a few years already supports

DROP TABLE IF EXISTS my_schema.my_table;
DROP TABLE IF EXISTS #my_table;
CREATE TABLE IF EXISTS my_schema.my_table;
CREATE TABLE IF EXISTS #my_table;

The syntax @Thomas_White mentioned is legacy code that still works, but the new syntax is preferred.

t