Drop Azure SQL table from Databricks notebook

Question

I am trying to drop a table in my Azure SQL server database (SQL version 2019) via my Databricks notebook.

In my notebook, I have established by jdbc connection to the azure sql server. I have a test query to confirm the connection. This works fine.

However, my code to drop a table on the SQL database does not fail but it does not drop the table. The table still exists.

table_name = "dbo.json_staging"
spark.sql(f"DROP TABLE IF EXISTS {table_name}")

Output: Out[35]: DataFrame[]

Answer 1

I have tried the below code to DROP the table from the Azure SQL database Using Azure Databricks.

I have tried it using the SCALA

%scala
import  java.util.Properties
import  java.sql.DriverManager
val  jdbcUsername = "admin02"
val  jdbcPassword = "Welcome@1"
val  driverClass = "com.microsoft.sqlserver.jdbc.SQLServerDriver"
val  jdbcUrl = s"jdbc:sqlserver://sqlserveraug09.database.windows.net:1433;database=db002;user=admin02@sqlserveraug09;password=Welcome@1;encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30;"
val  connectionProperties = new  Properties()
connectionProperties.put("user", s"${jdbcUsername}")
connectionProperties.put("password", s"${jdbcPassword}")
connectionProperties.setProperty("Driver", driverClass)
val  connection = DriverManager.getConnection(jdbcUrl, jdbcUsername, jdbcPassword)
val  stmt = connection.createStatement()
val  sql = "DROP Table employee_new"
stmt.execute(sql)
connection.close()

Output:

I have DROPED the Table named employee_new