ApexSQL Build feature highlight: Object dependency recognition for error free build scripts

To build a new database or update an existing one ApexSQL Build a SQL database deployment tool, can be used. It supports many different inputs for creating a database. In this article, the various ways in which dependencies are parsed to correctly order the scripts, no matter the database build input source, will be covered.

Building a deploy script directly from a database

ApexSQL Build allows specifying the objects to be created or updated and ensures error-free deployment by checking object dependencies before deploying or updating a database.

While running the deployment wizard, one step is Build objects. In the example below the Address table is excluded from the list of objects that will be created in the new database.

In the next step of the deployment wizard, Preview, in the Action summaries tab, the list with actions that will be executed while creating the database will be displayed. As it can be seen in the screenshot below, the table Address will be created even if it was deselected in the previous step of the deployment wizard.

This is done automatically by the application in order to preserve the inter-object dependencies and provide the error-free deployment of a database. If this action isn’t performed there would be errors during the database creation regarding the creation of database objects that are related to this table. The list of dependent objects that are related to the Table from the example above, can be seen in the Dependencies tab, as presented on the screenshot below.

If the table from the above example is by any reason omitted from the deployment script, errors like following would be included.

Msg 208, Level 16, State 1, Procedure vVendorWithAddresses, Line 3372
Invalid object name ‘Person.Address’.

Reordering DDL statements in an existing SQL script

The Object dependency recognition feature of ApexSQL Build enables the application to automatically analyze a SQL script, check it for dependencies and use that information to create a database deployment SQL script with properly ordered database object creation statements

This can be seen in the following example, where the input SQL script has an incorrect order, because the Customers table is created, after it is referenced

-- Create Foreign Key FK_Territories_Region on [dbo].[Territories]
Print 'Create Foreign Key FK_Territories_Region on [dbo].[Territories]'
GO
ALTER TABLE [dbo].[Territories]
	WITH NOCHECK
	ADD CONSTRAINT [FK_Territories_Region]
	FOREIGN KEY ([RegionID]) REFERENCES [dbo].[Region] ([RegionID])
ALTER TABLE [dbo].[Territories]
	CHECK CONSTRAINT [FK_Territories_Region]

GO

GO
-- Create Table [dbo].[Customers]
Print 'Create Table [dbo].[Customers]'
GO
CREATE TABLE [dbo].[Customers] (
		[CustomerID]       [nchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
		[CompanyName]      [nvarchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
		[ContactName]      [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
		[ContactTitle]     [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
		[Address]          [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
		[City]             [nvarchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
		[Region]           [nvarchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
		[PostalCode]       [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
		[Country]          [nvarchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
		[Phone]            [nvarchar](24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
		[Fax]              [nvarchar](24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
		CONSTRAINT [PK_Customers]
		PRIMARY KEY
		CLUSTERED
		([CustomerID])
)

This will cause the script to fail with errors like the following.

Create Index City on [dbo].[Customers]

Msg 1088, Level 16, State 12, Line 45
Cannot find the object "dbo.Customers" because it does not exist or you do not have permissions.

Using ApexSQL Build this problem is easily corrected and avoided. Select T-SQL as the output, and use the wrongly ordered SQL script as the input source, and start the deployment wizard. In the Preview step, in the Action summaries tab, it is shown that the Customers table will be created before the objects that are directly dependent on the Customers table.

Objects that are dependent on the Customers table can be seen in the Dependencies tab if you exclude the Customers table from the object build list. Those objects can be seen in the screenshot above, as ones that imply the creation of the Customers table, in order to maintain dependency relations, and provide the error-free deployment.

After completing the deployment wizard steps and creating the output T-SQL deployment script, the SQL code for creating the Customers table will be properly placed in the script, following the dependency rules.

GO
CREATE TABLE [dbo].[Customers] (
		[CustomerID]       [nchar](5) NOT NULL,
		[CompanyName]      [nvarchar](40) NOT NULL,
		[ContactName]      [nvarchar](30) NULL,
		[ContactTitle]     [nvarchar](30) NULL,
		[Address]          [nvarchar](60) NULL,
		[City]             [nvarchar](15) NULL,
		[Region]           [nvarchar](15) NULL,
		[PostalCode]       [nvarchar](10) NULL,
		[Country]          [nvarchar](15) NULL,
		[Phone]            [nvarchar](24) NULL,
		[Fax]              [nvarchar](24) NULL,
		CONSTRAINT [PK_Customers]
		PRIMARY KEY
		CLUSTERED
		([CustomerID])
)
GO
CREATE NONCLUSTERED INDEX [City]
	ON [dbo].[Customers] ([City])
GO
CREATE NONCLUSTERED INDEX [CompanyName]
	ON [dbo].[Customers] ([CompanyName])
GO
CREATE NONCLUSTERED INDEX [PostalCode]
	ON [dbo].[Customers] ([PostalCode])
GO
CREATE NONCLUSTERED INDEX [Region]
	ON [dbo].[Customers] ([Region])
GO
ALTER TABLE [dbo].[Customers] SET (LOCK_ESCALATION = TABLE)
GO

Combining individual scripts into a single build script

To deploy multiple SQL script files, into the same database, a batch file would have to be used, or some other solution would have to be created like manually ordering the scripts in the correct sequence, or splitting constraints and triggers out and executing at the end of the script. ApexSQL Build can help with merging and error-free deployment of multiple individual scripts into a single database deployment T-SQL script automatically

Using object dependency parsing and recognition, ApexSQL Build analyzes all scripts available in a script folder selected as the input source. The application will check dependencies for each script, merge the scripts, reorder the object creation based on the dependency relations, and create the SQL database deployment script as output.

To illustrate this we not only ordered the scripts once, but then added a new script to see what would happen. On the screenshot below, the left window represents the order of database object creation for a single example database. The right window represents the example script from the left window merged with another script from the same script folder. The new super-set of objects is properly ordered for error free execution automatically