In SSDT Schema Compare how do I ignore differences in objects of type "Schema"

Question

From the Schema Compare Options, I deselected all Object Types:

It still shows me differences in Schema objects:

I scrolled through the big list of General options, and none of them appeared to do this:

Burhans

What are the actual differences? If you script out the changes, what changes are made within those schemas? Permissions? Roles? Other? In this case, it may be that the schemas aren't in your project so it's trying to Drop them. Add them to project or choose not to drop items not in the project and you may have some success. – Carbine

They are not in my project, and I don't want them to be part of it. I want to drop other object types, like stored procedures. I just want to ignore schema objects, just like I am ignoring logins. It seems to be the only object type I cannot ignore. This is not a problem when I do a publish, since I can successfully ignore it. – Burhans

The problem is the Schema has different AUTHORIZATION [user] in Production and Dev. When I do publishing, the Schema get automatically checked (gray checkmark), when I select some Stored Procedures in that schema to be published. :((( I didn't find how to overcome this and had to publish part of stuff by hand. – Schoolbag

Answer 1

You can set the exclude schema in code by running the below as an exe before doing the schema merge. The below code needs the Microsoft.SqlServer.DacFx nuget package to be added to your project. It takes 2 parameters, one is the .scmp file path and second is comma separated string of schemas to exclude. It will overwrite the .scmp supplied and exclude the schema names you provided.

It essentially adds XML sections in the .scmp file that is equivalent to un-checking objects on the UI and saving the file. (persisted preference)

This exe execution can be a task in your VSTS (VSO) release pipeline, if you want to exclude one schema from being merged during deployment.

using System;
using System.Linq;
using System.Collections.Generic;
using Microsoft.SqlServer.Dac.Compare;

namespace DatabaseSchemaMergeHelper
{
    /// <summary>
    /// Iterates through a supplied schema compare file and excludes objects belonging to a supplied list of schema
    /// </summary>
    class Program
    {
        /// <summary>
        /// first argument is the scmp file to update, second argument is comma separated list of schemas to exclude
        /// </summary>
        /// <param name="args"></param>
        static void Main(string[] args)
        {
            if (args.Length == 0) return;

            var scmpFilePath = args[0];
            var listOfSchemasToExclude = args[1].Split(',').ToList();

            // load comparison from Schema Compare (.scmp) file
            var comparison = new SchemaComparison(scmpFilePath);
            var comparisonResult = comparison.Compare();

            // find changes pertaining to objects belonging to the supplied schema exclusion list
            var listOfDifferencesToExclude = new List<SchemaDifference>();

            // add those objects to a list
            foreach (SchemaDifference difference in comparisonResult.Differences)
            {
                if (difference.TargetObject != null &&
                    difference.TargetObject.Name != null &&
                    difference.TargetObject.Name.HasName &&
                    listOfSchemasToExclude.Contains(difference.TargetObject.Name.Parts[0], StringComparer.OrdinalIgnoreCase))
                {
                    listOfDifferencesToExclude.Add(difference);
                }
            }

            // add the needed exclusions to the .scmp file
            foreach (var diff in listOfDifferencesToExclude)
            {
                if (diff.SourceObject != null)
                {
                    var SourceExclusionObject = new SchemaComparisonExcludedObjectId(diff.SourceObject.ObjectType, diff.SourceObject.Name,
                                                                                     diff.Parent?.SourceObject.ObjectType, diff.Parent?.SourceObject.Name);
                    comparison.ExcludedSourceObjects.Add(SourceExclusionObject);
                }

                var TargetExclusionObject = new SchemaComparisonExcludedObjectId(diff.TargetObject.ObjectType, diff.TargetObject.Name,
                                                                                 diff.Parent?.TargetObject.ObjectType, diff.Parent?.TargetObject.Name);
                comparison.ExcludedTargetObjects.Add(TargetExclusionObject);
            }

            // save the file, overwrites the existing scmp.
            comparison.SaveToFile(scmpFilePath, true);
        }
    }
}

Bibi

Answer 2

I hacked it. If you save the compare, you can add this to the file:

  <PropertyElementName>
    <Name>Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlSchema</Name>
    <Value>ExcludedType</Value>
  </PropertyElementName>

You'll see where when you open it. This setting is not in the UI, but is apparently supported.

Burhans

In my case this didn't work. Saving this will actually exclude a lot more things rather than just excluding the schema. – Perlie

+1 to what @Kyopaxa mentioned - I had added this option into my SCMP file, but it masked a lot of other differences as well. I believe it may be related to this Microsoft Connect issue: connect.microsoft.com/VisualStudio/feedback/details/794077/… – Bewilder

this excludes all changes, even from other schema. It doesn't work. – Bibi

Answer 3

right-click on the top level nodes (Add, Change, Delete) you can choose "Exclude All" to uncheck all elements of that type. This will at least quickly get you to a state where everything is unchecked.

Alpinist