How to drop a external table from lake database in Synapse Analytics workspace?

Question

drop EXTERNAL table testdb.dbo.table_name

Error: Operation DROP EXTERNAL TABLE is not allowed for a replicated database.

Answer 1

@Rajendra kolli

This error usually mean you are trying to execute the operations in the context of a database synced from Spark (replicated database).
The Lake databases are replicated from the Apache Spark pool and managed by Apache Spark. So, you cannot drop objects in SQL Databases by using T-SQL language.

Please see this similar thread: https://learn.microsoft.com/en-us/answers/questions/746769/azure-synapse-unable-to-drop-created-table

You can use the below notebook command to drop your table.

spark.sql("Drop table if exists tablename")

https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/resources-self-help-sql-on-demand?tabs=x80070002#operation-isnt-allowed-for-a-replicated-database

Operation isn't allowed for a replicated database

This error is returned if you are trying to modify a Lake database, create external tables, external data sources, database scoped credentials or other objects in your Lake database. These objects can be created only on SQL databases.

The Lake databases are replicated from the Apache Spark pool and managed by Apache Spark. Therefore, you cannot create objects like in SQL Databases by using T-SQL language.

Only the following operations are allowed in the Lake databases:

• Creating, dropping, or altering views, procedures, and inline table-value functions (iTVF) in the schemas other than dbo.
• Creating and dropping the database users from Azure Active Directory.
• Adding or removing database users from db_datareader schema.

Other operations are not allowed in Lake databases.