Copy schema to another in Hana DB

You can get workfolder at backup console. Get destination folder and change backup as work for example.

[code language=”sql”]export “SOURCESCHEMA”.”*” as binary into ‘/workfolder’ with replace threads 10;[/code]

[code language=”sql”]import “SOURCESCHEMA”.”*” as binary from ‘/workfolder’ with rename schema “SOURCESCHEMA” to “TARGETSCHEMA”;[/code]

If you get privilege error like “SAP DBTech JDBC: [258]: insufficient privilege: Not authorized”, edit your user at Security menu and add EXPORT/IMPORT System Privileges.

Change all table unload priority in hana

Change all table priority for unload in short time.
[code lang=”sql”]
create procedure update_unload_priority() LANGUAGE SQLSCRIPT AS
CURSOR C_TABLE
FOR SELECT ‘ALTER TABLE “‘ || TABLE_NAME || ‘” UNLOAD PRIORITY 9;’ AS SQLTEXT
FROM SYS.TABLES
WHERE SCHEMA_NAME = ‘MYSCHEMA’ AND TABLE_TYPE = ‘COLUMN’ and temporary_table_type = ‘NONE’ AND unload_priority <> 9;
BEGIN
OPEN C_TABLE;
FOR cur_row AS C_TABLE DO
EXEC cur_row.SQLTEXT;
END FOR;
END;
[/code]

Convert all row table to column in Hana

You can convert all row table to column table with below procedure:
[code lang=”sql”]create procedure convert_column() LANGUAGE SQLSCRIPT AS
CURSOR C_TABLE
FOR SELECT ‘ALTER TABLE “‘ || TABLE_NAME || ‘” COLUMN;’ AS SQLTEXT
FROM SYS.TABLES
WHERE SCHEMA_NAME = ‘SAPABAP1’ AND TABLE_TYPE = ‘ROW’ and temporary_table_type = ‘NONE’;
BEGIN
OPEN C_TABLE;
FOR cur_row AS C_TABLE DO
EXEC cur_row.SQLTEXT;
END FOR;
END;
[/code]

You can execute below procedure like this:

[code lang=”sql”]
CALL convert_column;
[/code]

Unloading all column table in hana

You can use below procedure for unloading all column tables in Hana.
[code lang=”sql”]create procedure unload_all() LANGUAGE SQLSCRIPT AS
CURSOR C_TABLE
FOR SELECT ‘UNLOAD “’ || TABLE_NAME || ‘”;’ AS SQLTEXT
FROM SYS.TABLES
WHERE SCHEMA_NAME = ‘MYSCHEMA’ AND TABLE_TYPE = ‘COLUMN’ and temporary_table_type = ‘NONE’;
BEGIN
OPEN C_TABLE;
FOR cur_row AS C_TABLE DO
EXEC cur_row.SQLTEXT;
END FOR;
END;
[/code]

You call this procedure like below:

[code lang=”sql”]CALL unload_all;[/code]