Export Relational Model Data as Database Schema Create / Update Scripts

LLBLGen Pro can generate Database Schema Create and Update scripts in DDL SQL from the Relational Model Data in the project. There are two different script types: Database Schema Create Scripts and Database Schema Update Scripts. These are described more in detail below.

Info

There are no create / update scripts creatable for MS Access. Although MS Access can execute DDL SQL statements like CREATE TABLE, it can't do so in a batch, which makes it cumbersome to execute the statements.

Exporting Database Schema Update Scripts

Database schema update scripts update an existing database schema to a new version by manipulating the tables / constraints / fields etc. in the database schema. Every change made to the Relational Model Data which can be exported in an Update script, e.g. table rename, adding a field to a table, is tracked in the Relational Model Data storage and can be exported as a Database Schema Update Script. To do so, do one of the following:

  • Right-click the Relational Model Data node or a Relational Model Data storage node in Project Explorer and select Generate Database Schema Update Script (DDL SQL) from the context menu
  • Select Project -> Generate Database Schema Update Script (DDL SQL) from the main menu
  • Right-click the Relational Model Data node or a Relational Model Data storage node in Catalog Explorer and select Generate Database Schema Update Script (DDL SQL) from the context menu

If there are no changes found in the Relational Model Data, you can't export any Update script.

If there are changes found, LLBLGen Pro will present you the Code Generation Task Viewer](../Functionality%20Reference/CodeGenerationTaskViewer.htm) for you to configure the code generation process for the Update script. The target framework will be set to DDL SQL and only templates valid for that framework will be visible. The designer will automatically pre-select the right template group and preset.

The update script file will be generated with a time fragment in the filename. This will allow you to sort the files accordingly and execute them in the right order.

SQL Azure specific

If you generate SQL Server DDL SQL scripts and want to use them on SQL Database on Azure, you have to remove the USE [catalog] statements from the scripts, as SQL Database on Azure doesn't support the USE statement. If you have multiple catalogs in your project and you want to generate scripts for your databases on SQL Database for Azure, generate them per catalog as generating a single script from the catalog node in Catalog Explorer in the LLBLGen Pro designer will generate a single file with DDL SQL for all catalogs. In general this is fine but for SQL Database on Azure this gives a problem as it doesn't support multiple catalogs; each catalog is a separate database, so USE statements have no effect and give an error.

Script as starting point.

The Update script is meant to be the starting point of a migration process. When the database schema contains data, it's key to migrate that data along the way with the script. This requires that the script is adjusted to contain migration logic as well. Each step is commented so it should be easy to locate the area where migration logic should be added as well.

It can also be that some changes can't be scripted out. One of them is for example the drop of the identity flag on a field in SQL Server: this requires data migration with a temporary table. In these cases, the script tries to help by containing the action to take in comments so you can decide how to perform that action in practice: through migration, through other tools or through other scripts which take care of these particular cases.

Exporting Database Schema Create Scripts

Database Schema Create Scripts create a new schema from scratch. The create scripts contain drop statements for the created elements and these drop statements are commented out. Exporting a create script works similar to exporting an update script, with the difference that you can always export a create script, even if there are no changes. To export a create script do one of the following:

  • Right-click the Relational Model Data node or a Relational Model Data storage node in Project Explorer and select Generate Database Schema Create Script (DDL SQL) from the context menu
  • Select Project -> Generate Database Schema Create Script (DDL SQL) from the main menu
  • Right-click the Relational Model Data node or a Relational Model Data storage node in Catalog Explorer and select Generate Database Schema Create Script (DDL SQL) from the context menu

Similar to creating an update script, the Code Generation Task Viewer is used to configure the code generation process for the create script.

If there are changes found when you click the Create script option, you'll be notified that there are changes and that it's better to export the changes as an update script. If you decide to export a create script nonetheless, keep in mind that generating a create script resets all change trackers and after that you can't generate an update script with the tracked changes as the change trackers are reset.

Exporting scripts for multiple databases at once

When you select the create script option from the context menu opened on a Relational Model Data node, you'll export scripts for all databases in the project. If there are more than one database, the script for each database is placed in a subfolder named after the database in the destination root folder specified.