Renaming the database Name in Databricks

[Ramakrishnan83]

Team,

Initially our team created the databases with the environment name appended. Ex: cust_dev, cust_qa, cust_prod.

I am looking to standardize the database name as consistent name across environments. I want to rename to "cust". 

All of my tables are external tables in this database pointing to a storage in ADLS.  which option will help?

1. Does databricks supports rename command ?

2. Create a new database CUST and recreate all the tables in new database . Steps to be followed

 a. create database cust

b. drop table <table1> from cust_dev

c. create table cust.<table1>

What is recommended option?

#deltatable

[Hkesharwani]

In Databricks, you cannot directly rename a schema using a specific SQL statement like ALTER SCHEMA or RENAME SCHEMA. However, you can achieve a similar result by following these steps:

1. Create a new schema with the desired name using the CREATE SCHEMA statement.
2. Transfer all the (tables, views, functions, etc.) from the old schema to the new schema using ALTER TABLE, ALTER VIEW, or other appropriate statements. 
   ALTER TABLE old_schema.table_name SET SCHEMA new_schema;
   ALTER VIEW old_schema.view_name SET SCHEMA new_schema;
   
3. Update any references to the old schema in your code or queries to use the new schema name.
4. Finally, if you no longer need the old schema, you can drop it using the DROP SCHEMA statement.
   
   

[Ramakrishnan83]

Hi,

I don't see SET Schema as keyword in Alter table statement. https://docs.databricks.com/en/sql/language-manual/sql-ref-syntax-ddl-alter-table.html . 

I tried your statement. 

alter table ****.table_row_count SET SCHEMA ****.table_row_count

ParseException: no viable alternative at input 'alter table****.table_row_count SET SCHEMA'

[Hkesharwani]

Hi, 
Apologies, I just tried the above statement and they were not working. You may try creating the table with CTAS statements.

create table new_schema.table_name as select * from old_schema.table_name  

[roman_tkachuk]

It does not work if you need to keep partitioning or table properties.

Solutions:

1. CREATE TABLE .. LIKE ..
2. CREATE TABLE ... CLONE 

[bearded_data]

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`;​

[Avvar2022]

You can also use “CASCADE” to drop schema and tables as well. It is recursive.