CODEWITHSUNDEEP
sqlazureazure-synapse
Ken Masters
Ken Masters

Reputation: 281

How to copy all tables, stored procedures to another schema in Synapse data warehouse?

Currently, I need to transfer all tables (DDL and data in the tables), stored procedures to another schema in Synapse data warehouse. I checked the documentation below, but it seems that I have to move all of them one by one.

https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-schema-transact-sql?view=sql-server-ver15

Is there a method, command or query which I can transfer all the contents of a schema to another in Synapse data warehouse ?

Kind regards,

Upvotes: 2

Views: 2334

Answers (1)

wBob
wBob

Reputation: 14379

There is no built-in method to do this, but depending on your skills there are a number of different options:

  • use SQL Server Management Studio (SSMS) built-in scripting options. Newer versions of SSMS (v18.x and onwards) are capable of producing DDL for Azure Synapse Analytics. Simply point to your object (table, stored proc, view etc) in Object Explorer, right-click it, and view the scripting options. eg for tables you will see 'Script Table as'

  • SQL Server Data Tools (SSDT) - SSDT now has support for Azure Synapse Analytics, dedicated SQL pools. So you can import your schema, do a find and replace in the .sql scripts in the project, and generate the script. You can also use the Data Compare and Schema Compare features.

  • command-line option mssql-cli. This offers powerful command-line scripting options but you'll need to download and install it: https://learn.microsoft.com/en-us/sql/tools/mssql-cli?view=sql-server-ver15

  • Use CTAS to transfer schema and data. Create a simple CTAS template and run it for each of your tables:

     CREATE TABLE <new schema>.yourTable
 WITH
 (
     DISTRIBUTION = ROUND_ROBIN,
     CLUSTERED COLUMNSTORE INDEX
 )
 AS
 SELECT *
 FROM <old schema>.yourTable;
 OPTION ( LABEL = 'CTAS: copy yourTable to new schema' );

So a few options for you.

Upvotes: 3

Related Questions