If Table Exists Function

Some database systems such as MSSQL and MySQL provide a way to determine if a table exists using a statement like IF EXISTS. Most commonly it's used along side the DROP statement to drop a table if it exists because dropping a table that doesn't exist results in an error. That's not so much of a problem if you simply want to "fire and forget" but if your writing an installation script that installs/updates your product, the last thing you want is an error. Unfortunately as of writing, HANA doesn't have an equivalent IF EXISTS statement or function. Fortunately, a workaround is to create a custom function.


The task is fairly simple, create a function that takes two arguments and queries the OBJECTS system view and returns 1 for table exists and 0 for table doesn't exist.

Listing 1


RETURNS exists int 



    DECLARE _exists int := 0;

        CASE WHEN COUNT(*) > 0 THEN
        END INTO _exists

    exists = :_exists;


As you can see in listing 1, the function takes two varchar arguments. The first is the schema name and the second is the table name. The select statement queries the system OBJECTS view using the schema and table name. I've added an additional condition to ensure the object type is a table.

The function above can be used to test if a table exists in any specified schema. If you want to test if a table exists in the current schema then use the CURRENT_SCHEMA function as shown in listing 2 below.

Listing 2

Recent Articles