How to delete Database schema in Lakehouse ?

[VoltesDev]

Hi,

I accidentaly created a database schema in my Lakehouse (because it was created by Notebook and accidentally mistype) and I have deleted the table inside from the Lakehouse view it self, it is deleted after.

However if I switch it to SQL Endpoint, I still can see the schema in the tree. How is this happened and how to also delete this ? because I can't find button or menu to delete, unlike the menu in Lakehouse View.

thanks

[v-pnaroju-msft]

Hi VoltesDev,

We have not received a response from you regarding the query and were following up to check if you have found a resolution. If you have identified a solution, we kindly request you to share it with the community, as it may be helpful to others facing a similar issue.

If you find the response helpful, please mark it as the accepted solution and provide kudos, as this will help other members with similar queries.

Best regards,
Pavan

[v-pnaroju-msft · Accepted solution]

Hi @VoltesDev,

We appreciate your inquiry through the Microsoft Fabric Community Forum.

The Lakehouse and the SQL Endpoint in Microsoft Fabric represent different ways of interacting with the same underlying data. Below is an explanation of their functionalities and steps to resolve your issue:

1. Lakehouse View:
• This provides a file-system-oriented perspective where data is organized into folders and files, such as Parquet and Delta Lake formats.
• Actions performed in the Lakehouse View primarily affect the underlying data storage.
2. SQL Endpoint:
• This offers a SQL-based interface (using T-SQL) to query and manage data in the Lakehouse.
• The SQL Endpoint maintains its own metadata catalog, which includes schemas, tables, and views, independent of the file system structure.

When you delete a table through the Lakehouse View, only the associated data files are removed. However, the schema associated with the table remains intact in the SQL Endpoint's metadata. This behavior is intentional, as it allows users to recreate tables or reuse schemas for other purposes.

• Schemas in SQL are logical containers for database objects, such as tables, views, and stored procedures.
• Even if a schema is empty (i.e., contains no objects), it still exists as a logical entity in the SQL Endpoint's metadata.
• The Lakehouse View focuses on data files and does not provide an option to delete schemas, as it does not manage SQL metadata.

To remove the schema, you need to use T-SQL within the SQL Endpoint. Please follow the steps below:

1. Open a query editor connected to your Lakehouse's SQL Endpoint.
2. Execute the following T-SQL command to delete the schema:
   DROP SCHEMA SchemaName;
   Replace SchemaName with the actual name of the schema you wish to delete.
3. If the schema contains any other objects (ex: views or stored procedures), the DROP SCHEMA command will fail. You must delete those objects first using appropriate T-SQL commands.
   

If you find the response helpful, kindly mark it as the accepted solution and provide kudos. This will assist other community members facing similar queries.

Best regards,

Pavan.