SQL Change Automation 4

Offline schema model

The Offline schema model (OSM) is a read-only object level representation of your SQL Change Automation project and is updated whenever you generate migrations or apply changes. The OSM helps you to keep an audit of changes to your database objects when used with the history feature of your version control system.

The scripts generated as the OSM are grouped by object type into subfolders using their fully-qualified names. 

Example file structure

Schema-Model
|-- dbo
|	|-- Tables
|   |	|-- dbo.Customers.sql
|   |	|-- dbo.Orders.sql
|	|-- Types
|   |	|-- dbo.OrderNo.sql

Object types included in the offline schema model

The following items are included in the OSM by default:

  • Tables
    • Primary & Foreign keys
    • Constraints
    • Default values
    • Indices
    • DML triggers
  • Assemblies
  • Credentials
  • Defaults
  • File Groups
  • Full Text Catalogs/Indices/Stop Lists
  • Partition Schemes/Functions
  • Roles
  • Rules
  • Schemas
  • Search Property Lists
  • Services/Bindings/Queues/Contracts etc.
  • Sequences
  • Synonyms
  • User Defined Data Types
  • Users
  • XML Schema Collections

All files contain permission GRANT statements, where applicable, plus any extended properties attached to the objects.

Programmable objects

By default, programmable objects are not included in the OSM as they are already included in the programmable objects folder.

The programmable object types are:

  • Stored procedures
  • User defined functions
  • Views
  • Database DDL triggers

Configuration

Grouping

By default,  SQL Change Automation  groups the objects in your OSM folder by schema and then by object type (for example,  dbo\Tables\MyTable.sql ). If, however, you would like to revert to the default SQL Compare behavior, grouping only by object type and prefixing each file with the appropriate schema name (for example,  Tables\dbo.MyTable.sql ), you can do so by changing the SyncToOfflineSchemaModelBySchema  setting in the project settings file .

<PropertyGroup>
  <SyncToOfflineSchemaModelBySchema>False</SyncToOfflineSchemaModelBySchema>
</PropertyGroup>

Controlling which object types to include

You can customize which objects get included in the Offline Schema Model by editing your .sqlproj file:

<PropertyGroup>
 <SyncToOfflineSchemaModelObjectTypes>
   Table;Assembly;Contract;Credential;Default;EventNotification;FileGroup;FullTextCatalog;FullTextIndex;
   FullTextIndexColumn;FullTextStopList;FullTextStopListStopWord;MessageType;PartitionFunction;PartitionScheme;
   Queue;RemoteServiceBinding;Role; Route;Rule;Schema;SearchPropertyList;Sequence;Service;Synonym;Users;
   UserDefinedType;XmlSchemaCollection;StoredProcedures;Functions;Views;DDLTriggers
 </SyncToOfflineSchemaModelObjectTypes>
</PropertyGroup>

This is a full list of supported Offline Schema Model object types, including Programmable Objects. You can remove items from the list to control which objects should be included.

Disabling the offline schema model

The OSM can be disabled by editing your .sqlproj file:

<PropertyGroup>
  <SyncToOfflineSchemaModel>False</SyncToOfflineSchemaModel>
</PropertyGroup>

Learn more



Didn't find what you were looking for?