How to customize the SQL Server database objects to be deployed in a database build process (4213596)

Whenever building a SQL Server database, it must determine which objects to include in it. ApexSQL Build provides a detailed object analysis for building a database or for a database update deployment, with an ability to customize the SQL database objects and include dependent objects automatically. Selecting objects is easy and allows to easily customize SQL database objects.

In the Build objects step the DDL script related to the analyzed object can be previewed with a click on the View DDL script button:

This makes checking the object itself easier as there is no need to move focus away from ApexSQL Build and use additional tools to preview the DDL script. In the below example, SQL database table was selected to preview its DDL script:

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_PADDING ON
GO

CREATE TABLE [HumanResources].[Department] (
 [DepartmentID] [smallint] IDENTITY(1, 1) NOT NULL,
 [Name] [dbo].[Name] NOT NULL,
 [GroupName] [dbo].[Name] NOT NULL,
 [ModifiedDate] [datetime] NOT NULL
 )
GO

ALTER TABLE [HumanResources].[Department] ADD CONSTRAINT 
 [DF_Department_ModifiedDate] DEFAULT(getdate())
FOR [ModifiedDate]
GO

CREATE UNIQUE NONCLUSTERED INDEX [AK_Department_Name] ON [HumanResources].
 [Department] ([Name])
GO

ALTER TABLE [HumanResources].[Department]

SET (LOCK_ESCALATION = TABLE)
GO

Now, let’s see what actually happens with object dependencies and what information is offered, when only specific SQL database objects are selected. All of the objects created/added and actions taken are summarized under the Preview step, in two separate tabs.

The Action summaries tab provides information about selected and dependent SQL database objects. It will show the summary of actions that will be performed, such as creating a function, schema, table, etc. Additionally, for an object, ApexSQL Build will create the index, add a primary key, default constraints and other related objects if they haven’t been included initially:

The Dependencies tab allows a preview of the selected SQL database objects and those that depend on them. This analysis mechanism ensures that no dependency is broken if only certain objects are selected for the build:

ApexSQL Build offers additional Options related to the objects scripting process that will allow to customize SQL database objects script in a way to match different requirements:

The available options that can be found in the Home tab, from the Options window, under the Script tab:

Build options

• Include static data – insert static data from the input source into tables
• Include transaction handling – wraps the created script statements into explicit transactions. Any errors that occur during the script execution will roll back the transactions and leave the database in the initial state
• Script Ansi options – adds the Set Ansi_padding On/Off and Set Ansi nulls On/Off options to the output script before each affected object
• Script collations (tables only) – adds collation specification for character columns to the output script in Create table statements
• Script drops – adds Drop statements to the output script before each SQL database object, before the corresponding Create statement is scripted
• Script extended properties – scripts the extended properties for object types, where applicable
• Script filegroups – scripts the available database filegroups with the existence check
• Script logins with users – script SQL Server logins that the scripted database users are based on
• Script names – scripts index names, foreign key names and constraint names
• Script permissions – scripts granted, denied or revoked permissions on objects, where applicable
• Script triggers with parent table or view – scripts existing table/view triggers along with the parent object
• Script Use for databases – adds Use [database name] statements to the output script

Optional output elements

• Header - inserts header options to the output script
  • Author – adds author tag to the header
  • Date and time – adds current date and time in the header
  • Legal – adds legal tag to the header
• Comments – adds comments to the output script before each SQL database object
• Print statements – adds Print statements to the output script before each object

A quick example; if the Script extended properties option is checked and used with a table (Department in this case), the script will include extended properties for specified object types:

ALTER TABLE [HumanResources].[Department] ADD CONSTRAINT 
 [PK_Department_DepartmentID] PRIMARY KEY CLUSTERED ([DepartmentID])
GO

EXEC sp_addextendedproperty N'MS_Description',
 N'Primary key (clustered) constraint',
 'SCHEMA',
 N'HumanResources',
 'TABLE',
 N'Department',
 'CONSTRAINT',
 N'PK_Department_DepartmentID'
GO

EXEC sp_addextendedproperty N'MS_Description',
 N'Clustered index created by a primary key constraint.',
 'SCHEMA',
 N'HumanResources',
 'TABLE',
 N'Department',
 'INDEX',
 N'PK_Department_DepartmentID'
GO

With ApexSQL Build, every deployment (building or upgrading) process can be customized and adjusted to any use case scenario. ApexSQL Build will ensure that changes are propagated without problems or errors, so that building a SQL database is much easier and more effective.