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.
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.
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;
...
HANA DB Articles
-
JSON_TABLE Function
In this article, we'll take a look at how to use the ++JSON_TABLE++ function to represent JSON data stored in a regular column as a relational table.
31 March 2023 - 2696 views -
Connect To HANA DB Using Rust
Learn how to connect and query a HANA database using Rust with the hdbconnect package.
21 February 2023 - 2758 views -
Connect To HANA DB Using NodeJs
This article explains how to connect to a SAP HANA system using the Node HANA client library.
28 October 2019 - 7128 views -
Connect To HANA DB Using Python
This article explains how to connect to a SAP HANA system using the Python hdbcli package.
11 October 2018 - 7979 views -
Adding Auto Numbers To Table Columns
This article explains how to create an auto generated column using IDENTITY.
14 March 2018 - 6253 views -
Connecting To SAP HANA Using PHP ODBC
This article describes how to install and configure PHP odbc to connect to a SAP HANA system on Ubuntu 16.
01 December 2017 - 7640 views -
Connect To HANA DB Using Java
This article describes how to connect to a HANA DB system using the ngdbc.jar Java driver.
03 November 2017 - 5332 views