Moving SQL table definitions, but the partition definition is different

I'm creating a SQL datamart archive that has a handful of tables that are partitioned. The table definition will be tracked so that changes are moved over, but I want to exclude the partition definition as the archive partition definition will be different.

The partition function, schema definition and even the date column on which the partition is acted will be different.


Is there a way at I can exclude the partition definition from the comparison, so that only column changes are migrated?

Parents Reply
  • I tried doing that.

    But the table definition still contains the partition scheme reference.

    Below is an example:

    Source is this --

    CREATE TABLE [Raw].[MySourceTable](
    [ID] [int] NOT NULL,
    [ClientId] [int] NULL,
    [DeviceId] [int] NULL,
    [Occurred] [smalldatetime] NULL,
    [Count] [smallint] NULL,
    [Load_ID] [int] NULL
    ) ON [ParitionSchemeDefSmallDate]([Occurred])

    Destination should be this --

    CREATE TABLE [Raw].[MySourceTable](
    [ID] [int] NOT NULL,
    [ClientId] [int] NULL,
    [DeviceId] [int] NULL,
    [Occurred] [smalldatetime] NULL,
    [Count] [smallint] NULL,
    [Load_ID] [int] NULL
    ) ON [psAnnualPartition]([Occurred])

    or 

    CREATE TABLE [Raw].[MySourceTable](
    [ID] [int] NOT NULL,
    [ClientId] [int] NULL,
    [DeviceId] [int] NULL,
    [Occurred] [smalldatetime] NULL,
    [Count] [smallint] NULL,
    [Load_ID] [int] NULL

Children