A recurring question of folks using SAP HANA is how to only run a
DROP command for objects that actually exist in the database. One could argue that the effect of executing
DROP does not depend on whether the object to be dropped exists, because after running the command it will not be there either way, but there is a bit more to it.
DROP commands, like say
DROP TABLE, report back an error message when the object cannot be found (e.g. because it does not exist).
drop table bla; Could not execute 'drop table bla' SAP DBTech JDBC: : invalid table name: BLA: line 1 col 12 (at pos 11)
This can be annoying when running scripts to set up a database structure which is a common part of product installation and update procedures.
On MS SQL Server there exists (ha ha ha – extra point for the flattest pun) an extension to the
DROP command (and to other DDL commands): DROP IF EXISTS
SAP HANA up to now (HANA 2 SPS 03) does not provide such a functionality, but it is easy to come up with a workaround.
The following utility function can be used:
drop function obj_exists ; create function obj_exists (in schema_name NVARCHAR(256) , in object_name NVARCHAR(256)) returns obj_exists int as begin declare _exists int := 0; select case when count(*) >0 then 1 else 0 end into _exists from objects where schema_name = IFNULL (:schema_name, current_schema) and object_name = IFNULL (:object_name, ''); obj_exists = :_exists; end;
The function returns 1 if the object exists and 0 if it does not. If the input parameters contain
NULL, then the function still works using the
CURRENT schema but will likely return 0 as output.
select obj_exists (schema_name => NULL , object_name => 'OBJ_EXISTS') as OBJ_EXISTS from dummy; OBJ_EXISTS 1
The above worked because the
OBJ_EXISTS function was created in the current schema. With that one can easily write a SQL Script that “looks before it fires“.