There was a use case to clear a specified schema in a Test HANA system. And the schema was having good number of tables, table types and views. And writing DROP statement manually was a lengthy task. In order to automate this task, I thought of writing a stored procedure in HANA. Stored procedure should also have displayed a message on deletion or completion of task. Below are details about stored procedure and usage of it.
1.1 Summary -
Document illustrates a SQL Script stored Procedure, implemented in SAP HANA (SP09), to delete various objects (Tables/Views/Types) of a Schema. Objects Technical names are Case sensitive.
1.2 Description –
Stored procedure has Input Parameters Schema and Object Type (TABLE/TYPE/VIEW). Values of Object Type should be given in upper case. Stored procedure has Output Parameter which displays a list of objects deleted.
1.3 Procedure SQL Script code –
/* *********************************************************************** **
** PROCEDURE: TEST_DEL_TAB_WRAPPER **
** Author : Basha Shaik, Client: Defense, May 12, 2015 11:28 **
** DESCRIPTION: **
** Procedure has input parameters SCHEMA and OBJECT TYPE **
** Procedure reads tables SYS.OBJECTS and SYS.TABLES **
** Dynamic SQL statement performs deletion **
** *********************************************************************** */
/********* Begin Procedure Script ************/
BEGIN
declare lv_count integer := 0;
declare lv_init integer := 1;
declare lv_tabnm1 nvarchar(100);
declare lv_schema1 nvarchar(50);
declare lv_obj_type nvarchar(50);
declare lv_full_tabnm varchar(100);
CREATE LOCAL TEMPORARY TABLE #OP_TEXT_1 ( TEXT_OP NVARCHAR(100));
if :ip_obj_type = 'TABLE' then
p_sys_objects =
select distinct T1.SCHEMA_NAME, T1.OBJECT_NAME, T1.OBJECT_TYPE from
(select distinct SCHEMA_NAME, OBJECT_NAME, OBJECT_TYPE
from SYS.OBJECTS
where
SCHEMA_NAME = :ip_schema and OBJECT_TYPE = 'TABLE'
order by SCHEMA_NAME, OBJECT_NAME, OBJECT_TYPE) T1
inner join
(select distinct SCHEMA_NAME, TABLE_NAME, TABLE_TYPE, IS_USER_DEFINED_TYPE
from SYS.TABLES
where
SCHEMA_NAME = :ip_schema and IS_USER_DEFINED_TYPE = 'FALSE'
order by SCHEMA_NAME, TABLE_NAME, TABLE_TYPE, IS_USER_DEFINED_TYPE) T2
on T1.SCHEMA_NAME = T2.SCHEMA_NAME
and T1.OBJECT_NAME = T2.TABLE_NAME;
elseif :ip_obj_type = 'TYPE' then
p_sys_objects =
select distinct T1.SCHEMA_NAME, T1.OBJECT_NAME, 'TYPE' as OBJECT_TYPE from
(select distinct SCHEMA_NAME, OBJECT_NAME, OBJECT_TYPE
from SYS.OBJECTS
where
SCHEMA_NAME = :ip_schema and OBJECT_TYPE = 'TABLE'
order by SCHEMA_NAME, OBJECT_NAME, OBJECT_TYPE) T1
inner join
(select distinct SCHEMA_NAME, TABLE_NAME, TABLE_TYPE, IS_USER_DEFINED_TYPE
from SYS.TABLES
where
SCHEMA_NAME = :ip_schema and IS_USER_DEFINED_TYPE = 'TRUE'
order by SCHEMA_NAME, TABLE_NAME, TABLE_TYPE, IS_USER_DEFINED_TYPE) T2
on T1.SCHEMA_NAME = T2.SCHEMA_NAME
and T1.OBJECT_NAME = T2.TABLE_NAME;
elseif :ip_obj_type = 'VIEW' then
p_sys_objects =
select distinct SCHEMA_NAME, OBJECT_NAME, OBJECT_TYPE
from SYS.OBJECTS
where
SCHEMA_NAME = :ip_schema and OBJECT_TYPE = 'VIEW'
order by SCHEMA_NAME, OBJECT_NAME, OBJECT_TYPE;
end if;
select count(*) into lv_count from :p_sys_objects;
op_param = select distinct SCHEMA_NAME, OBJECT_NAME,
ROW_NUMBER() OVER (partition by SCHEMA_NAME, OBJECT_TYPE order by OBJECT_NAME) asrow_num,
:lv_count as TOT_COUNT, OBJECT_TYPE from :p_sys_objects;
if :lv_count > 0 then
while (:lv_init <= :lv_count)
do
select distinct SCHEMA_NAME into lv_schema1 from :op_param
where
row_num = :lv_init;
select distinct OBJECT_NAME into lv_tabnm1 from :op_param
where
row_num = :lv_init;
select distinct OBJECT_TYPE into lv_obj_type from :op_param
where
row_num = :lv_init;
if (:lv_schema1 is not null and :lv_tabnm1 is not null) then
insert into #op_text_1 select 'Deleted: '|| :lv_obj_type || ' '||'"'||:lv_schema1||'"'||'.'||'"'||:lv_tabnm1||'"' as TEXT_OP from dummy;
select 'DROP '|| :lv_obj_type || ' '||'"'||:lv_schema1||'"'||'.'||'"'||:lv_tabnm1||'"' into lv_full_tabnm from dummy;
EXEC :lv_full_tabnm;
end if;
lv_init := :lv_init + 1;
end while;
end if;
if :lv_count = 0 then
insert into #op_text_1 select 'No Entries Found' as TEXT_OP from dummy;
end if;
OP_TEXT = select TEXT_OP from #OP_TEXT_1;
drop table #OP_TEXT_1;
END;
/********* End Procedure Script ************/
1.4 Test Results –
A. Schema TEST has tables and views as below –
TABLE1, TABLE2, Table3 & VIEW1
B. Execute procedures
1.4 Result –
Tables and Views deleted from Schema.
