How to re-order scripts to avoid dependency-based errors

Having a database scripted to a script folder or to a source control, it enables to use these DDL scripts to recreate it. However, it might not go smoothly due to some missing dependencies.

When creating a database object from scratch, the needed objects are created along the way. The objects can be tied in relationships and constraints to make them dependent on each other.

Creating objects from existing scripts is a bit different. The database structure is not known, along with its dependencies and constraints. If a table that utilizes a user-defined type that doesn’t exist in the database is created, the following error message will be encountered:

Msg 2715, Level 16, State 6, Line 2
Column, parameter, or variable #11: Cannot find data type dbo.Flag.

Something similar happens when a database object that depends on another, non-existent object is created:

Msg 208, Level 16, State 1, Procedure vEmployeeDepartment, Line 8
Invalid object name 'HumanResources.Employee'.

The solution is to create the parent objects first, and then create the objects that reference them.

This would be easy if the database only had a couple of objects and a dependency schema is known.
Unfortunately, this is not usually the case.

One of the methods is to create database objects by type:

1. Create all tables, keys, triggers and indexes
2. Functions
3. Views
4. Stored procedures

But, what happens if the ViewEmployee view references the ViewEmployeeHistory view? The ViewEmployeeHistory view has to be created first. So, the above method might not always work.

How to execute the object scripts in a correct order

ApexSQL Build is a SQL database deployment tool that builds new databases or updates existing ones by packaging SQL scripts, script folders, database snapshots and even scripts under source control into a single deployment SQL script, a C# solution or an executable installer. It allows specifying the objects to be created or updated, ensures error-free deployment and more.

1. Start ApexSQL Build
2. Under the Home tab, in the Database section, click the Build button to initiate the build process:

   

3. Select the Database option as an output type:

   

4. Select the Script folder as an input source:

   

5. Click the Browse button and select the folder that contains the DDL scripts for the database objects
6. Specify the server name and authentication way where the new database will be created in the Connect to SQL Server step:
   

7. Under the Database options step, provide a database name and other database properties such as compatibility mode, collation, etc.:

   

8. In the Build objects step, a list of the objects to be created will be displayed. They are all selected by default. If some of them are not needed, uncheck them:

   

9. In the Preview step, the summary of actions for the execution will be shown. After reviewing them, click the Next button to continue:

   

10. In the last Output options step, choose whether to save a copy of the newly created build script under the Script tab:

    

    If the Save a copy of script options is chosen, the created script can be reviewed or modified and re-execute it afterwards, directly in SQL Server Management Studio.

    Additionally, the Header can be inserted, along with the Version number of the build script:

    

11. Click the Finish button

12. Once the execution of the build script is done, the Results window will be shown:

    

The script will create a new database and all objects in it.

Creating a database from a bunch of DDL scripts doesn’t have to be a problem. ApexSQL Build provides the optimal object creation order so that all objects are created successfully and no problems with missing dependencies are encountered.