If Table Exists Function

31 October 2019 - 92 views
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.

TABLE_EXISTS 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

CREATE FUNCTION TABLE_EXISTS(IN schema_name VARCHAR(256), IN table_name VARCHAR(256))

RETURNS exists int 

AS

BEGIN

    DECLARE _exists int := 0;

    SELECT 
        CASE WHEN COUNT(*) > 0 THEN
            1
        ELSE 
            0
        END INTO _exists
    FROM OBJECTS WHERE OBJECT_TYPE='TABLE' AND SCHEMA_NAME=:schema_name AND OBJECT_NAME=:table_name;

    exists = :_exists;

END;

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

CREATE FUNCTION TABLE_EXISTS(IN table_name VARCHAR(256))
...
FROM OBJECTS WHERE OBJECT_TYPE='TABLE' AND SCHEMA_NAME=CURRENT_SCHEMA AND OBJECT_NAME=:table_name;
...