Search code examples
sql-serverazurecreate-tableexternal-tables

Is there a way to automatically generate a script in Azure SQL containing the create instructions for every table in the database

I have an existing data base in Azure that I need to replicate in another Azure database as External tables. To this end I need to make a script that creates all these 200+ tables. I’m looking for a way to automatically produce the table creation script for all these tables so I can modify this to make the new External tables on the other database.

I know I can do this manually by generating the individual tables script from the Server Management Studio and making the script from adding all them to a script, but with over 200 tables this is going to be very time consuming.

Is there a simple way to do this?

Solution

  • Yes, if you don't mind it being non-automated.

    From SSMS, right click the database in Object Explorer. Choose "Tasks/Generate Scripts..." option and follow the wizard.

    You will be able to choose exactly which objects are scripted & with which options.

    I've successfully used this to transfer on-prem to Azure SQL db schemas.

    The output can be stored as a single text file, which you can additionally modify if required.

    You do not need to specify individual tables, although you will be able to modify the table list (eg, if you have a handful you'd rather not transfer)

    I had less success with choosing the "Azure DB" target types & eventually just generated a SQL2019 compatible script, which ran without error.

    As commented by @testing-ma-lady - (i) the script generator does not take care of dependency sequencing. This affects particularly affects FK references and means entries populating the dependency tables for programmability objects (sp, fn etc) are not always present. (ii) Be very careful to check the Advanced Settings for the scripting types, eg, indexes are not included by default and your detailed requirements are unlikely to exactly match the defaults.

    For automation, it may be possible to use sqlpackage export, which will generate a BACPAC file. This is a composite compressed file which normally includes the data content as well, but you may be able to export from a template db or open & remove the data content from the archive. before rezipping it.