bearded_data
New Contributor III

Since you can't simply rename the schema, here are the steps you need to follow in order to "Rename a Schema". 

Create new Schema. 

 

CREATE SCHEMA `catalog`.`new_schema_name`;​

 

For every object in the schema create a copy in the new schema.  Sample CTAS for a table move shown below, but you will also need to do this with other objects in the schema (functions, views, models, etc.).

 

CREATE TABLE `catalog`.`new_schema_name`.`table_name` 
AS 
SELECT * 
FROM `catalog`.`old_schema_name`.`table_name`;​

Drop the old objects in the old schema. Sample drop for tables shown below, but you will also need to do this
with other objects in the schema (functions, views, models, etc.). 

 

DROP TABLE `catalog`.`old_schema_name`.`table_name`;​

 

Once you've dropped all objects and have a blank schema, drop the schema. 

 

DROP SCHEMA `catalog`.`old_schema_name`;​