ApexSQL Build feature highlight: Using an individual script file to create error free deployment script

ApexSQL Build is a tool used to deploy new and/or update existing SQL Server databases. To perform these tasks, it can use several input types, e.g. scripts, schema snapshots, source control projects/labels, etc.

Individual script file

An individual script file that can be used as the source type in ApexSQL Build, is a SQL script file containing DDL statements required to build a database or database objects. The individual script file can contain one or more DDL statements for creation of one or more database objects or an entire database

This individual SQL script can be processed into a C# project or .NET executable “installer”, executed against one or more databases directly, or reprocessed, to a new script, that has individual create statements ordered to prevent execution errors.

Using a script file as the source

ApexSQL Build has an option to use a single script file as the source to create an error free database deployment script or installation package.

To use a single SQL script as the source, the Script file option is used in the input source step of the deployment wizard.

The Script folder option is used for multiple scripts as the input source, which can be several large database creation scripts or hundreds of smaller object creation scripts for example.

Script file

Selecting Script file as the source allows the selection of a single SQL script that contains the code necessary to build a database. By the first look this seems simple enough, SQL script goes in, T-SQL script comes out, but it isn’t. Thanks to the object dependency recognition feature, the input SQL script will be analyzed for the dependency relation of objects within, and if necessary, corrected by re-ordering object creation scripts to provide error-free output T-SQL script.

Also, before creating the deployment T-SQL script, database objects that are present in the input script can be deselected from the list of database objects that will be created in the output script, providing that there are no dependency relations connected to them. If there are objects that are dependent on the object that is deselected from the database build list, deselected object will be automatically recreated to ensure error-free deployment:

Re-ordering individual script DDL statements to prevent errors

There are several advantages of using ApexSQL Build to create deployment SQL scripts. The first and foremost is the ability to analyze input individual scripts for dependencies and to re-order them when creating a deployment script to ensure error-free deployment. This feature can correct the database creation scripts that are returning error, when executed, due to the wrong order of object creation based on the dependency relations.

An example of this would be the following excerpt from the input script, in which the statement for creating the table [Person].[AddressType] is wrongly ordered and the script is returning errors when executed due to a fact that this table is related to one table and three views that require that table to be created in the exact order.

-- Create Index IX_WorkOrderRouting_ProductID on [Production].[WorkOrderRouting]
Print 'Create Index IX_WorkOrderRouting_ProductID on [Production].[WorkOrderRouting]'
GO
CREATE NONCLUSTERED INDEX [IX_WorkOrderRouting_ProductID]
	ON [Production].[WorkOrderRouting] ([ProductID])
GO
ALTER TABLE [Production].[WorkOrderRouting] SET (LOCK_ESCALATION = TABLE)
GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON

GO
-- Create Table [Person].[AddressType]
Print 'Create Table [Person].[AddressType]'
GO
CREATE TABLE [Person].[AddressType] (
		[AddressTypeID]     [int] IDENTITY(1, 1) NOT NULL,
		[Name]              [dbo].[Name] NOT NULL,
		[rowguid]           [uniqueidentifier] NOT NULL ROWGUIDCOL,
		[ModifiedDate]      [datetime] NOT NULL,
		CONSTRAINT [PK_AddressType_AddressTypeID]
		PRIMARY KEY
		CLUSTERED
		([AddressTypeID])
)
GO
-- Add Default Constraint DF_AddressType_ModifiedDate to [Person].[AddressType]
Print 'Add Default Constraint DF_AddressType_ModifiedDate to [Person].[AddressType]'
GO
ALTER TABLE [Person].[AddressType]
	ADD
	CONSTRAINT [DF_AddressType_ModifiedDate]
	DEFAULT (getdate()) FOR [ModifiedDate]
GO

After executing script with this order, it would fail and return errors like the following:

Msg 15135, Level 16, State 8, Procedure sp_addextendedproperty, Line 5469
Object is invalid. Extended properties are not permitted on ‘Sales.vStoreWithAddresses’, or the object does not exist.

This kind of problem can be resolved using ApexSQL Build and the script file as the source. After selecting the desired output type and using the wrongly ordered script file as the source, database properties need to be set and after that the deployment wizard will parse the input script for dependencies. Next step of the deployment wizard will be to display the list of all objects that will be created using the input script:

After selecting the objects that will be included in the deployment script and proceeding to the next step of the deployment wizard, the proper order of the statement execution will be displayed in the Preview represented by objects. The table [Person].[AddressType] will be re-ordered and placed properly in the deployment script based on the dependency relations mentioned at the beginning of the example:

Those dependency relations can be checked if the table [Person].[AddressType] is unchecked in the Build objects step of the deployment wizard, and in the next step select the Dependencies tab, where the table and views mentioned at the beginning of the example will be displayed.

After completing the deployment wizard steps and saving the deployment script, upon opening it, the DDL statement for creating the [Person].[AddressType] table will be properly placed, ensuring the error free deployment.

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_PADDING ON
GO
CREATE TABLE [Person].[CountryRegion] (
		[CountryRegionCode]     [nvarchar](3) NOT NULL,
		[Name]                  [dbo].[Name] NOT NULL,
		[ModifiedDate]          [datetime] NOT NULL,
		CONSTRAINT [PK_CountryRegion_CountryRegionCode]
		PRIMARY KEY
		CLUSTERED
		([CountryRegionCode])
)
GO
ALTER TABLE [Person].[CountryRegion]
	ADD
	CONSTRAINT [DF_CountryRegion_ModifiedDate]
	DEFAULT (getdate()) FOR [ModifiedDate]
GO
CREATE UNIQUE NONCLUSTERED INDEX [AK_CountryRegion_Name]
	ON [Person].[CountryRegion] ([Name])
GO
ALTER TABLE [Person].[CountryRegion] SET (LOCK_ESCALATION = TABLE)
GO
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_PADDING ON
GO
CREATE TABLE [Person].[AddressType] (
		[AddressTypeID]     [int] IDENTITY(1, 1) NOT NULL,
		[Name]              [dbo].[Name] NOT NULL,
		[rowguid]           [uniqueidentifier] NOT NULL ROWGUIDCOL,
		[ModifiedDate]      [datetime] NOT NULL,
		CONSTRAINT [PK_AddressType_AddressTypeID]
		PRIMARY KEY
		CLUSTERED
		([AddressTypeID])
)
GO
ALTER TABLE [Person].[AddressType]
	ADD
	CONSTRAINT [DF_AddressType_ModifiedDate]
	DEFAULT (getdate()) FOR [ModifiedDate]
GO
ALTER TABLE [Person].[AddressType]
	ADD
	CONSTRAINT [DF_AddressType_rowguid]
	DEFAULT (newid()) FOR [rowguid]
GO
CREATE UNIQUE NONCLUSTERED INDEX [AK_AddressType_Name]
	ON [Person].[AddressType] ([Name])
GO
CREATE UNIQUE NONCLUSTERED INDEX [AK_AddressType_rowguid]
	ON [Person].[AddressType] ([rowguid])
GO
ALTER TABLE [Person].[AddressType] SET (LOCK_ESCALATION = TABLE)
GO
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_PADDING OFF
GO
CREATE TABLE [Person].[BusinessEntity] ()

For this example a T-SQL script was used as the output option. The individual script file can be also deployed directly to a database, or can be packaged into C# project or .NET executable.