How do you handle the table definition for one with a non default schema?

[JohnGoldInc]

IF SCHEMA_ID(N'Security') IS NULL EXEC(N'CREATE SCHEMA [Security];');
GO
CREATE TABLE [Security].[CategoryType] (
    [CategoryID] int NOT NULL IDENTITY,
    [CategoryName] nvarchar(15) NOT NULL,
    [Description] ntext NULL,
    [Picture] image NULL,
    CONSTRAINT [PK_Categories] PRIMARY KEY ([CategoryID])
);

errors on build with:

[Security].[CategoryType] has an unresolved reference to SqlSchema [Security]. Northwind.Sql

[jmezach]

@JohnGoldInc Thanks for reporting. What happens if you move the CREATE SCHEMA to a separate file? I'm fairly certain this works since we've been doing this for ages.

[JohnGoldInc]

@jmezach I had tried it as a separate file previously.

Found my solution though, just had to remove the IF SCHEMA_ID(N'Security') IS NULL stuff and put CREATE SCHEMA [Security]; in a separate file

[JohnGoldInc]

@jmezach getting out of the habit of putting IF NOT EXISTS's around creates is the major trick to this way of doing things it seems :)

[ErikEJ]

@JohnGoldInc Think of the .dacpac scripts as describing the desired state, so just always use CREATE.

The DacFX / sqlpackage magic juice takes care of bringing the target database to the desired state without data loss.