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
  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 .NET solution or an executable installer. It allows specifying the objects to be created or updated, ensures error-free deployment, enables executing SQL scripts against multiple SQL Servers simultaneously and more.

  1. Start ApexSQL Build
  2. In the Build tab, select the Build a new database action:

  3. Select the Database option as an output type:

    Select 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 properties for the new database. If the properties are scripted in one of the scripts, click the Get database properties from the input source button:

  7. 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:

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

  9. In the last step, choose whether to save a copy of the newly created script and to insert header into it:

    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.

  10. Click the Build button

    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.

April 4, 2013