Pull code from on prem database to create AZURE SQL table including indexes, constraints, keys, etc. using pyspark

Question

I need to fetch the 'CREATE TABLE' code with all primary keys,indexes, collation etc. from on prem SQL Server db to Databricks and then from Databricks to Azure SQL db.

I have already connection to both SQL Server databases from Databricks. I'm using JDBC driver.

Answer 1

There's no built-in TSQL api to generate full-fidelity DDL for existing objects. The Sql Server Management Objects (SMO) .NET library is the official component that does DDL generation.

So not the most natural thing to use from Databricks, but technically possible.

Users have created more or less elaborate TSQL solutions for this for decades. See: How to generate create script of table using SQL query in SQL Server for some links.

If you don't need full fidelity, you can just use Spark JDBC to open a DataFrame from one SQL Server and write it to the other.