如何在VS/DevOps中修改Schema Compare脚本(.scmp)以排除特定表和存储过程

Great question! I’ve tackled this exact scenario with my own team when standardizing SQL Server object management in VS Database Projects. Here are three straightforward ways to exclude your _BU-suffix backup tables and specific stored procedures directly within the VS/DevOps ecosystem:

1. Manually Edit the .scmp XML File #

The .scmp file is just an XML document under the hood, so you can add wildcard-based exclusion rules directly:

• Locate your existing .scmp file in your solution. Right-click it in VS, select Open With > XML Editor.
• Look for the <SchemaCompareSettingsService> section. If there’s no <ExcludeObjects> node, add it inside this section.
• Add filter entries for your target object types. For your backup tables and stored procedures, add these lines:

  <ExcludeObjects>
    <FilterEntry Type="SqlTable" Value="%_BU%" />
    <FilterEntry Type="SqlStoredProcedure" Value="%_BU%" />
  </ExcludeObjects>
  

  • Use % as the wildcard (matches any character sequence)
  • Valid Type values include SqlTable, SqlStoredProcedure, SqlView, etc.—match the object type you want to exclude
• Save the file, then reopen it in Schema Compare. Your _BU tables/procs will now be excluded automatically.

2. Configure Filters via the VS UI (Then Share the .scmp) #

If you prefer a visual approach instead of editing XML:

• Open Schema Compare in VS, load your source/target databases/projects.
• Click the Options gear icon in the top-right corner.
• Switch to the Objects tab, then scroll to the Exclude objects section.
• Click Add, then:
  • For tables: Select Table as the object type, enter %_BU% in the Name field, and check Use wildcard.
  • Repeat for stored procedures: Select Stored Procedure, enter your wildcard pattern (e.g., %_BU% or a specific name).
• Once all filters are set, go to File > Save As and save the .scmp file to your team’s shared solution directory. Ensure all team members use this shared file instead of creating their own.

3. Integrate Exclusions into Azure DevOps Pipelines #

If your team uses Azure DevOps for CI/CD, you can mirror these exclusion rules in your schema compare pipeline task:

• In your YAML pipeline, add the SqlSchemaCompare task and include the excludeObjects parameter with your wildcard rules:

  - task: SqlSchemaCompare@1
    inputs:
      sourceType: 'SqlDacpac'
      source: '$(Build.ArtifactStagingDirectory)/YourDatabase.dacpac'
      targetType: 'SqlDatabase'
      targetConnection: 'YourTargetDBConnectionString'
      excludeObjects: |
        SqlTable:%_BU%
        SqlStoredProcedure:%_BU%
  

This ensures your automated comparisons align with the manual VS comparisons your team uses daily.

Quick Tips #

• Test your filters: Create a test table like DimCustomer_BU20220419 and run Schema Compare to confirm it doesn’t show up in the results.
• Keep the shared .scmp updated: If new exclusion rules are needed, update the shared file instead of letting team members configure their own—this maintains consistency across the team.

内容的提问来源于stack exchange，提问作者laurens