Every now and then while running, installing, and copying JAVA AS for SAP on SQL Server you may run into an issue where the wrong schema is set for a set of tables in the database. This is not a problem if you are willing to completely start from scratch and reinstall Java. For most people, however, that would present a significant problem. Changing the schema of these tables is really the best option in this scenario.
In order to change the schema, you need to use the ALTER SCHEMA command in SQL.
The syntax is as follows:
ALTER SCHEMA new_schema TRANSFER old_schema.table_name
This works like a charm if you are trying to change the schema of a single table. However, there are over 300 JAVA tables for SAP, and your fingers can get pretty tired trying to run this command for each and every one individually.
Try this procedure to change the schema of all tables in a particular schema to a new one:
SELECT
'ALTER SCHEMA new_schema TRANSFER old_schema. '+name FROM sys.tables
WHERE schema_id = x
In this simple statement, you will need to insert your values for new_schema and old_schema as well as the schema_id for old_schema. You can find the schema_id by identifying a table in the old_schema and running the following query:
SELECT schema_id from sys.tables WHERE name = table_name
Hope this helps...
