How to tell SqlPackage.exe to create object existence check script?

Question

I have this code that creates a migration script for me:

First Part (Extract)

sqlpackage /a:extract 
 /of:true 
 /scs:"Data Source =.; Initial Catalog = Accounts; user id = sqluser; password = sqluser123;" 
 /tf:C:\Temp\SchemaCompare\Shop\Accounts\Source.dacpac

Second Part (DepolyReport)

sqlpackage /a:deployreport 
 /op:C:\Temp\SchemaCompare\Shop\Accounts\Report.xml 
 /of:True 
 /sf:C:\Temp\SchemaCompare\Shop\Accounts\Source.dacpac 
 /tcs:"Data Source=.;Initial Catalog=Shop;user id=sqluser;password=sqluser123;" 
 /P:DropObjectsNotInSource=True 
 /P:DropPermissionsNotInSource=False 
 /P:IgnorePermissions=True 
 /mp:10

Third Part (Script)

sqlpackage /a:script 
 /op:C:\Temp\SchemaCompare\Shop\Accounts\Migration.sql 
 /of:True 
 /sf:C:\Temp\SchemaCompare\Shop\Accounts\Source.dacpac 
 /tcs:"Data Source=.;Initial Catalog=Shop;user id=sqluser;password=sqluser123;" 
 /P:DropObjectsNotInSource=True 
 /P:DropPermissionsNotInSource=False 
 /P:IgnorePermissions=True 
 /mp:10

This works well. But it does not create the object existence check script.

For example, it creates this script:

CREATE TABLE [accounts].[Users] (
 [Id] BIGINT IDENTITY (1, 1) NOT NULL,
 [UserName] NVARCHAR (256) NULL,
 [NormalizedUserName] NVARCHAR (256) NULL,
 [Email] NVARCHAR (256) NULL,
 CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED ([Id] ASC)
);

But I want it to create this script:

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[accounts].[Users]') AND type in (N'U'))
BEGIN
CREATE TABLE [accounts].[Users](
 [Id] [bigint] IDENTITY(1,1) NOT NULL,
 [UserName] [nvarchar](256) NULL,
 [NormalizedUserName] [nvarchar](256) NULL,
 [Email] [nvarchar](256) NULL,
 CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED 
(
 [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END

Is there a way to configure sqlpackage.exe to do that?

Update

The reason we need this functionality is because we have this problem that, we use synonyms and sometimes those synonyms should be changed after deployment, which means we have to run the difference script twice. Once for the initial objects to be made (synonyms would be made in this phase), then we run synonym correction scripts (to change synonyms to refer to objects in the production environment) and then rerun the script to let views be made (they can't be made in the first step).

A synonym like [shopping].[Basket] would refer to [ShopModule].shopping.[Basket] in the development environment, but it should refer to [Shop].[shopping].[Basket] in the production environment.

This means views that use this synonym, won't be made the first time we run comparison script.

Thus we came with this solution to run our script twice. First time, tables would be made. Then we fix synonyms on the server (we append fix script to the end of our migration script). Then we run it another time to let views be built. If we had existence check we could do this. But now it's impossible.

Answer 1

SqlPackage follows a state-based database deployment paradigm. That means

• you define a source (Source.dacpac, which you derived from a database named "Accounts")
• you define the target (which you defined as a database named "Shop")
• SqlPackage is responsible for identifying the differences (this is what you see in the DeployReport step)
• SqlPackage is responsible for generating a script to bring the target in line with the source

You won't see those "IF EXISTS" type of checks on objects, because SSDT generates the script based on the state of the target database. In other words, it already knows if [accounts].[Users] exists and generates the script accordingly (to create it, alter it, or ignore it if it already matches the source dacpac).

This approach is not very tolerant of an environment where changes to the target might be made between when the script was generated and when it was run.

The only time I can recall seeing an existence check in these scripts is when the database itself is being created for the first name - in which case the script will check to see if a database by the requested name already exists.