Change schema name or move table

[Former Member]

1. Can I move table from one schema to another ?

2. Can I change schema name? I have schema SYSTEM and I need to have new name XYZ.

[Former Member]

Thank you for your clear answer.

[patrickbachmann]

Hey Tomas, where this post is a few years old already I was just wondering if anything has changed and if there is now an easy way to rename a schema?

Thanks,

-Patrick

[Former Member]

Hi Patrick, about your question, you can do an export/import of schema in SAP HANA Studio (through command line also). Before the import do a full backup of the SAP HANA Instance. In the import phase use the "RENAME SCHAME" option.

Best regards from Chile

@Fepezoabarca

[patrickbachmann]

Hi Felipe,

What about the data in the tables?  Would the import/export just be the meta-data?  Essentially I want to rename the schema and have all the data in tact.

Thanks,

-Patrick

[Former Member]

Hi Patrick,

    When you do the export from HANA Studio, you can select the options for catalog export and data export, something like this:

See the SAP Note 1936989.

   Also you can use the SWPM for do the export/import process and rename the schema. When you do the import process, select the option Standard System Copy / Migration (Load-Based), like this:

Best Regards from Chile

@Fepezoabarca

[patrickbachmann]

Very cool!  Thanks Felipe!

[Former Member]

This video can help you for the import process in SAP HANA Studio:

SAP HANA Academy - EXPORT/IMPORT: Using With Rename Schema option during import - YouTube.

[tomas-krojzl]

Hello,

1. Can I move table from one schema to another ?

Yes, you can migrate the table from one schema to another.

This can be done either by export / import (if between databases) or if within one database internally by CREATE TABLE statement.

2. Can I change schema name? I have schema SYSTEM and I need to have new name XYZ.

No, but you can create new schema and migrate all objects into this new schema.

You can rename the table (within same schema):

RENAME TABLE <schema>.<table name> TO <new table name>

You can make copy using CREATE TABLE statement:

CREATE COLUMN TABLE <target schema>.<table name>
LIKE <source schema>.<table name> WITH DATA

Note: If this is row table - just remove keyword COLUMN.

After copy and validation you might need to drop old table:

DROP TABLE <source schema>.<table name>

Please see following blogs about how to perform this operation using export/import (but this is not preferred if within one database). It is also covering also migration of models. You need to migrate models using these tables to adjust the reference to the moved table.

SAP HANA - Modeling Content Migration - part 1: Preparations, Security Requirements, Export

SAP HANA - Modeling Content Migration - part 2: Import, Post-migration Activities

Tomas

Edited by: Tomas Krojzl on Dec 13, 2011 12:54 PM