How to check if table already exists in SAP HANA

[Former Member]

hi all,

i would like to know how to drop a table if it does already exist,

so i was trying to use the if exists statement to do this, but it seem to be not working:

for example, if my table name is DATA_TAB, i did the following:

if exists (select * from DATA_TAB)

(

drop table type DATA_TAB

)

the correct  syntax for the SQLScript is

if exists (select * from DATA_TAB)

then

drop table type DATA_TAB

end if;

still not working;

Any one have an idea how to do this?

Thanks & Regards

Mohamed Ali

[Former Member · Accepted solution]

ok, i found a solution for dropping a table in this thread:

http://www.saphana.com/thread/1486

which basically is: to create a new procedure to check if the table in question

does exist and dropped if it does. Here is the procedure to be created.

drop procedure existstable;
create procedure existstable
( IN tablename VARCHAR(20), IN schemaname varchar(20)
) LANGUAGE SQLSCRIPT AS myrowid integer;

BEGIN
myrowid := 0;
select count(*) into myrowid from "PUBLIC"."M_TABLES" where schema_name =:schemaname and table_name=:tablename;

IF (:myrowid > 0 ) then
exec 'DROP TABLE '||:schemaname||'.'||:tablename;

END IF;

End;

it turned out that the data type table information is not stored in the same

location as the table information:

in other terms:  the user created table information is stored

in the table "PUBLIC"."M_TABLES", now I am looking for the table

in which the user created types information are stored  in order to create

a procedure as the one above to drop a type.

i hope i am clear enough;

Does any one know what is the name of the table holding information about the user created type

Thanks & regards

Mohamed Ali

[former_member184768 · Accepted solution]

Hi,

Please check the view TABLES.

select * from tables where table_name like '%your_table_type%';

Regards,

Ravi

[Former Member · Accepted solution]

hi to all, i created a procedure to check if a type already exists and delete in this case. I inspired the proc from this thread http://www.saphana.com/thread/1486 and the answer of Ravi. And share it with you here in case someone needs it:

drop procedure existsType;

create procedure existsType

( IN tablename VARCHAR(20), IN schemaname varchar(20)

) LANGUAGE SQLSCRIPT AS myrowid integer;

BEGIN

myrowid := 0;

select count(*) into myrowid from tables where schema_name =:schemaname and table_name=:tablename;

IF (:myrowid > 0 ) then

exec 'DROP TABLE '||:schemaname||'.'||:tablename;

END IF;

End;

best regards

Mohamed Ali

[Former Member · Accepted solution]

Hi ravi,

How to view the table ?

Thanks

[former_member184768 · Accepted solution]

What exactly do you need to view ? You can search for the table in the studio by going to the system -> Catalog -> (Application_User) -> right click -> Find table.

Ravi