Source project (left):AdventureWorks.sqlproj
Target database (right):[.\SQL11].[AdventureWorks_PRODUCTION]
Path:C:\BuildAgent\work\8d14ea28c3031417\db\AdventureWorks\AdventureWorks.sqlproj
Date generated:2013-04-18 16:00:20
Migrations included:
  • Deploy-Once\0025_Create-MyTable.sql
  • Deploy-Once\0026_Drop-ProductionCulture-Table.sql
  • Deploy-Once\0027_Drop-ProductionCulture-Refs.sql
  • Deploy-Once\0028_Expand-JobTitle-Column.sql

    • Selected Report Options:

    XDifferent Objects
     Equal Objects
    XSource Only Objects
    XDestination Only Objects
    Objects specified by "ExcludeObjectsFromImport" property

    Comparison Results By Object:

    Procedures

    Name Schema In Source In Destination Diff Type View SQL
    uspGetWhereUsedProductID dbo   X << View SQL
    uspUpdateEmployeeHireInfo HumanResources X X <> View SQL

    Tables

    Name Schema In Source In Destination Diff Type View SQL
    MyTable dbo X   >> View SQL
    Employee HumanResources X X <> View SQL
    Culture Production   X << View SQL
    ProductModelProductDescriptionCulture Production X X <> View SQL

    Views

    Name Schema In Source In Destination Diff Type View SQL
    vProductAndDescription Production X X <> View SQL

    Differences By Object:


    Procedure //[dbo].[uspGetWhereUsedProductID] (back to Summary)
    Source projectTarget database
      1SET ANSI_NULLS ON
      2SET QUOTED_IDENTIFIER ON
      3GO
      4CREATE PROCEDURE [dbo].[uspGetWhereUsedProductID]
      5@StartProductID [int],
      6@CheckDate [datetime]
      7AS
      8BEGIN
      9SET NOCOUNT ON;
      10--Use recursive query to generate a multi-level Bill of Material (i.e. all level 1 components of a level 0 assembly, all level 2 components of a level 1 assembly)
      11WITH [BOM_cte]([ProductAssemblyID], [ComponentID], [ComponentDesc], [PerAssemblyQty], [StandardCost], [ListPrice], [BOMLevel], [RecursionLevel]) -- CTE name and columns
      12AS (
      13SELECT b.[ProductAssemblyID], b.[ComponentID], p.[Name], b.[PerAssemblyQty], p.[StandardCost], p.[ListPrice], b.[BOMLevel], 0 -- Get the initial list of components for the bike assembly
      14FROM [Production].[BillOfMaterials] b
      15INNER JOIN [Production].[Product] p
      16ON b.[ProductAssemblyID] = p.[ProductID]
      17WHERE b.[ComponentID] = @StartProductID
      18AND @CheckDate >= b.[StartDate]
      19AND @CheckDate <= ISNULL(b.[EndDate], @CheckDate)
      20UNION ALL
      21SELECT b.[ProductAssemblyID], b.[ComponentID], p.[Name], b.[PerAssemblyQty], p.[StandardCost], p.[ListPrice], b.[BOMLevel], [RecursionLevel] + 1 -- Join recursive member to anchor
      22FROM [BOM_cte] cte
      23INNER JOIN [Production].[BillOfMaterials] b
      24ON cte.[ProductAssemblyID] = b.[ComponentID]
      25INNER JOIN [Production].[Product] p
      26ON b.[ProductAssemblyID] = p.[ProductID]
      27WHERE @CheckDate >= b.[StartDate]
      28AND @CheckDate <= ISNULL(b.[EndDate], @CheckDate)
      29)
      30-- Outer select from the CTE
      31SELECT b.[ProductAssemblyID], b.[ComponentID], b.[ComponentDesc], SUM(b.[PerAssemblyQty]) AS [TotalQuantity] , b.[StandardCost], b.[ListPrice], b.[BOMLevel], b.[RecursionLevel]
      32FROM [BOM_cte] b
      33GROUP BY b.[ComponentID], b.[ComponentDesc], b.[ProductAssemblyID], b.[BOMLevel], b.[RecursionLevel], b.[StandardCost], b.[ListPrice]
      34ORDER BY b.[BOMLevel], b.[ProductAssemblyID], b.[ComponentID]
      35OPTION (MAXRECURSION 25)
      36END;
      37GO
      38

    Procedure [HumanResources].[uspUpdateEmployeeHireInfo]//[HumanResources].[uspUpdateEmployeeHireInfo] (back to Summary)
    Source projectTarget database
    1SET ANSI_NULLS ON1SET ANSI_NULLS ON
    2SET QUOTED_IDENTIFIER ON2SET QUOTED_IDENTIFIER ON
    3GO3GO
    4CREATE PROCEDURE [HumanResources].[uspUpdateEmployeeHireInfo]4CREATE PROCEDURE [HumanResources].[uspUpdateEmployeeHireInfo]
    5@BusinessEntityID [int],5@BusinessEntityID [int],
    6@JobTitle [nvarchar](100),6@JobTitle [nvarchar](50),
    7@HireDate [datetime],7@HireDate [datetime],
    8@RateChangeDate [datetime],8@RateChangeDate [datetime],
    9@Rate [money],9@Rate [money],
    10@PayFrequency [tinyint],10@PayFrequency [tinyint],
    11@CurrentFlag [dbo].[Flag]11@CurrentFlag [dbo].[Flag]
    12WITH EXECUTE AS CALLER12WITH EXECUTE AS CALLER
    13AS13AS
    14BEGIN14BEGIN
    15SET NOCOUNT ON;15SET NOCOUNT ON;
    16BEGIN TRY16BEGIN TRY
    17BEGIN TRANSACTION;17BEGIN TRANSACTION;
    18UPDATE [HumanResources].[Employee]18UPDATE [HumanResources].[Employee]
    19SET [JobTitle] = @JobTitle19SET [JobTitle] = @JobTitle
    20,[HireDate] = @HireDate20,[HireDate] = @HireDate
    21,[CurrentFlag] = @CurrentFlag21,[CurrentFlag] = @CurrentFlag
    22WHERE [BusinessEntityID] = @BusinessEntityID;22WHERE [BusinessEntityID] = @BusinessEntityID;
    23INSERT INTO [HumanResources].[EmployeePayHistory]23INSERT INTO [HumanResources].[EmployeePayHistory]
    24([BusinessEntityID]24([BusinessEntityID]
    25,[RateChangeDate]25,[RateChangeDate]
    26,[Rate]26,[Rate]
    27,[PayFrequency])27,[PayFrequency])
    28VALUES (@BusinessEntityID, @RateChangeDate, @Rate, @PayFrequency);28VALUES (@BusinessEntityID, @RateChangeDate, @Rate, @PayFrequency);
    29COMMIT TRANSACTION;29COMMIT TRANSACTION;
    30END TRY30END TRY
    31BEGIN CATCH31BEGIN CATCH
    32-- Rollback any active or uncommittable transactions before32-- Rollback any active or uncommittable transactions before
    33-- inserting information in the ErrorLog33-- inserting information in the ErrorLog
    34IF @@TRANCOUNT > 034IF @@TRANCOUNT > 0
    35BEGIN35BEGIN
    36ROLLBACK TRANSACTION;36ROLLBACK TRANSACTION;
    37END37END
    38EXECUTE [dbo].[uspLogError];38EXECUTE [dbo].[uspLogError];
    39END CATCH;39END CATCH;
    40END;40END;
    41GO41GO
    42 42 

    Table [dbo].[MyTable]// (back to Summary)
    Source projectTarget database
    1SET ANSI_NULLS ON  
    2SET QUOTED_IDENTIFIER ON  
    3  
    4GO  
    5CREATE TABLE [dbo].[MyTable] (  
    6 [Id] [int] NOT NULL,  
    7 [MyCol] [nchar](10) NOT NULL  
    8) ON [PRIMARY]  
    9GO  
    10ALTER TABLE [dbo].[MyTable]  
    11 ADD  
    12 CONSTRAINT [MyConstraintName2]  
    13 PRIMARY KEY  
    14 CLUSTERED  
    15 ([Id])  
    16 ON [PRIMARY]  
    17GO  
    18ALTER TABLE [dbo].[MyTable]  
    19 ADD  
    20 CONSTRAINT [MyConstraintName1]  
    21 CHECK  
    22 ((1)=(2))  
    23GO  
    24ALTER TABLE [dbo].[MyTable]  
    25CHECK CONSTRAINT [MyConstraintName1]  
    26GO  
    27ALTER TABLE [dbo].[MyTable]  
    28 ADD  
    29 CONSTRAINT [DF_MyTable_MyCol]  
    30 DEFAULT ('') FOR [MyCol]  
    31GO  
    32ALTER TABLE [dbo].[MyTable]  
    33 ADD  
    34 CONSTRAINT [MyConstraintName]  
    35 DEFAULT ((0)) FOR [Id]  
    36GO  
    37ALTER TABLE [dbo].[MyTable] SET (LOCK_ESCALATION = TABLE)  
    38GO  
    39  

    Table [HumanResources].[Employee]//[HumanResources].[Employee] (back to Summary)
    Source projectTarget database
    1SET ANSI_NULLS ON1SET ANSI_NULLS ON
    2SET QUOTED_IDENTIFIER ON2SET QUOTED_IDENTIFIER ON
    33
    4GO4GO
    5CREATE TABLE [HumanResources].[Employee] (5CREATE TABLE [HumanResources].[Employee] (
    6 [BusinessEntityID] [int] NOT NULL,6 [BusinessEntityID] [int] NOT NULL,
    7 [NationalIDNumber] [nvarchar](15) NOT NULL,7 [NationalIDNumber] [nvarchar](15) NOT NULL,
    8 [LoginID] [nvarchar](256) NOT NULL,8 [LoginID] [nvarchar](256) NOT NULL,
    9 [OrganizationNode] [hierarchyid] NULL,9 [OrganizationNode] [hierarchyid] NULL,
    10 [OrganizationLevel] AS ([OrganizationNode].[GetLevel]()),10 [OrganizationLevel] AS ([OrganizationNode].[GetLevel]()),
    11 [JobTitle] [nvarchar](100) NOT NULL,11 [JobTitle] [nvarchar](50) NOT NULL,
    12 [BirthDate] [date] NOT NULL,12 [BirthDate] [date] NOT NULL,
    13 [MaritalStatus] [nchar](1) NOT NULL,13 [MaritalStatus] [nchar](1) NOT NULL,
    14 [Gender] [nchar](1) NOT NULL,14 [Gender] [nchar](1) NOT NULL,
    15 [HireDate] [date] NOT NULL,15 [HireDate] [date] NOT NULL,
    16 [SalariedFlag] [dbo].[Flag] NOT NULL,16 [SalariedFlag] [dbo].[Flag] NOT NULL,
    17 [VacationHours] [smallint] NOT NULL,17 [VacationHours] [smallint] NOT NULL,
    18 [SickLeaveHours] [smallint] NOT NULL,18 [SickLeaveHours] [smallint] NOT NULL,
    19 [CurrentFlag] [dbo].[Flag] NOT NULL,19 [CurrentFlag] [dbo].[Flag] NOT NULL,
    20 [rowguid] [uniqueidentifier] NOT NULL ROWGUIDCOL,20 [rowguid] [uniqueidentifier] NOT NULL ROWGUIDCOL,
    21 [ModifiedDate] [datetime] NOT NULL21 [ModifiedDate] [datetime] NOT NULL
    22) ON [PRIMARY]22) ON [PRIMARY]
    23GO23GO
    24ALTER TABLE [HumanResources].[Employee]24ALTER TABLE [HumanResources].[Employee]
    25 ADD25 ADD
    26 CONSTRAINT [PK_Employee_BusinessEntityID]26 CONSTRAINT [PK_Employee_BusinessEntityID]
    27 PRIMARY KEY27 PRIMARY KEY
    28 CLUSTERED28 CLUSTERED
    29 ([BusinessEntityID])29 ([BusinessEntityID])
    30 ON [PRIMARY]30 ON [PRIMARY]
    31GO31GO
    32ALTER TABLE [HumanResources].[Employee]32ALTER TABLE [HumanResources].[Employee]
    33 ADD33 ADD
    34 CONSTRAINT [CK_Employee_BirthDate]34 CONSTRAINT [CK_Employee_BirthDate]
    35 CHECK35 CHECK
    36 ([BirthDate]>='1930-01-01' AND [BirthDate]<=dateadd(year,(-18),getdate()))36 ([BirthDate]>='1930-01-01' AND [BirthDate]<=dateadd(year,(-18),getdate()))
    37GO37GO
    38ALTER TABLE [HumanResources].[Employee]38ALTER TABLE [HumanResources].[Employee]
    39CHECK CONSTRAINT [CK_Employee_BirthDate]39CHECK CONSTRAINT [CK_Employee_BirthDate]
    40GO40GO
    41ALTER TABLE [HumanResources].[Employee]41ALTER TABLE [HumanResources].[Employee]
    42 ADD42 ADD
    43 CONSTRAINT [CK_Employee_Gender]43 CONSTRAINT [CK_Employee_Gender]
    44 CHECK44 CHECK
    45 (upper([Gender])='F' OR upper([Gender])='M')45 (upper([Gender])='F' OR upper([Gender])='M')
    46GO46GO
    47ALTER TABLE [HumanResources].[Employee]47ALTER TABLE [HumanResources].[Employee]
    48CHECK CONSTRAINT [CK_Employee_Gender]48CHECK CONSTRAINT [CK_Employee_Gender]
    49GO49GO
    50ALTER TABLE [HumanResources].[Employee]50ALTER TABLE [HumanResources].[Employee]
    51 ADD51 ADD
    52 CONSTRAINT [CK_Employee_HireDate]52 CONSTRAINT [CK_Employee_HireDate]
    53 CHECK53 CHECK
    54 ([HireDate]>='1996-07-01' AND [HireDate]<=dateadd(day,(1),getdate()))54 ([HireDate]>='1996-07-01' AND [HireDate]<=dateadd(day,(1),getdate()))
    55GO55GO
    56ALTER TABLE [HumanResources].[Employee]56ALTER TABLE [HumanResources].[Employee]
    57CHECK CONSTRAINT [CK_Employee_HireDate]57CHECK CONSTRAINT [CK_Employee_HireDate]
    58GO58GO
    59ALTER TABLE [HumanResources].[Employee]59ALTER TABLE [HumanResources].[Employee]
    60 ADD60 ADD
    61 CONSTRAINT [CK_Employee_MaritalStatus]61 CONSTRAINT [CK_Employee_MaritalStatus]
    62 CHECK62 CHECK
    63 (upper([MaritalStatus])='S' OR upper([MaritalStatus])='M')63 (upper([MaritalStatus])='S' OR upper([MaritalStatus])='M')
    64GO64GO
    65ALTER TABLE [HumanResources].[Employee]65ALTER TABLE [HumanResources].[Employee]
    66CHECK CONSTRAINT [CK_Employee_MaritalStatus]66CHECK CONSTRAINT [CK_Employee_MaritalStatus]
    67GO67GO
    68ALTER TABLE [HumanResources].[Employee]68ALTER TABLE [HumanResources].[Employee]
    69 ADD69 ADD
    70 CONSTRAINT [CK_Employee_SickLeaveHours]70 CONSTRAINT [CK_Employee_SickLeaveHours]
    71 CHECK71 CHECK
    72 ([SickLeaveHours]>=(0) AND [SickLeaveHours]<=(120))72 ([SickLeaveHours]>=(0) AND [SickLeaveHours]<=(120))
    73GO73GO
    74ALTER TABLE [HumanResources].[Employee]74ALTER TABLE [HumanResources].[Employee]
    75CHECK CONSTRAINT [CK_Employee_SickLeaveHours]75CHECK CONSTRAINT [CK_Employee_SickLeaveHours]
    76GO76GO
    77ALTER TABLE [HumanResources].[Employee]77ALTER TABLE [HumanResources].[Employee]
    78 ADD78 ADD
    79 CONSTRAINT [CK_Employee_VacationHours]79 CONSTRAINT [CK_Employee_VacationHours]
    80 CHECK80 CHECK
    81 ([VacationHours]>=(-40) AND [VacationHours]<=(240))81 ([VacationHours]>=(-40) AND [VacationHours]<=(240))
    82GO82GO
    83ALTER TABLE [HumanResources].[Employee]83ALTER TABLE [HumanResources].[Employee]
    84CHECK CONSTRAINT [CK_Employee_VacationHours]84CHECK CONSTRAINT [CK_Employee_VacationHours]
    85GO85GO
    86ALTER TABLE [HumanResources].[Employee]86ALTER TABLE [HumanResources].[Employee]
    87 ADD87 ADD
    88 CONSTRAINT [DF_Employee_CurrentFlag]88 CONSTRAINT [DF_Employee_CurrentFlag]
    89 DEFAULT ((1)) FOR [CurrentFlag]89 DEFAULT ((1)) FOR [CurrentFlag]
    90GO90GO
    91ALTER TABLE [HumanResources].[Employee]91ALTER TABLE [HumanResources].[Employee]
    92 ADD92 ADD
    93 CONSTRAINT [DF_Employee_ModifiedDate]93 CONSTRAINT [DF_Employee_ModifiedDate]
    94 DEFAULT (getdate()) FOR [ModifiedDate]94 DEFAULT (getdate()) FOR [ModifiedDate]
    95GO95GO
    96ALTER TABLE [HumanResources].[Employee]96ALTER TABLE [HumanResources].[Employee]
    97 ADD97 ADD
    98 CONSTRAINT [DF_Employee_rowguid]98 CONSTRAINT [DF_Employee_rowguid]
    99 DEFAULT (newid()) FOR [rowguid]99 DEFAULT (newid()) FOR [rowguid]
    100GO100GO
    101ALTER TABLE [HumanResources].[Employee]101ALTER TABLE [HumanResources].[Employee]
    102 ADD102 ADD
    103 CONSTRAINT [DF_Employee_SalariedFlag]103 CONSTRAINT [DF_Employee_SalariedFlag]
    104 DEFAULT ((1)) FOR [SalariedFlag]104 DEFAULT ((1)) FOR [SalariedFlag]
    105GO105GO
    106ALTER TABLE [HumanResources].[Employee]106ALTER TABLE [HumanResources].[Employee]
    107 ADD107 ADD
    108 CONSTRAINT [DF_Employee_SickLeaveHours]108 CONSTRAINT [DF_Employee_SickLeaveHours]
    109 DEFAULT ((0)) FOR [SickLeaveHours]109 DEFAULT ((0)) FOR [SickLeaveHours]
    110GO110GO
    111ALTER TABLE [HumanResources].[Employee]111ALTER TABLE [HumanResources].[Employee]
    112 ADD112 ADD
    113 CONSTRAINT [DF_Employee_VacationHours]113 CONSTRAINT [DF_Employee_VacationHours]
    114 DEFAULT ((0)) FOR [VacationHours]114 DEFAULT ((0)) FOR [VacationHours]
    115GO115GO
    116ALTER TABLE [HumanResources].[Employee]116ALTER TABLE [HumanResources].[Employee]
    117 WITH CHECK117 WITH CHECK
    118 ADD CONSTRAINT [FK_Employee_Person_BusinessEntityID]118 ADD CONSTRAINT [FK_Employee_Person_BusinessEntityID]
    119 FOREIGN KEY ([BusinessEntityID]) REFERENCES [Person].[Person] ([BusinessEntityID])119 FOREIGN KEY ([BusinessEntityID]) REFERENCES [Person].[Person] ([BusinessEntityID])
    120ALTER TABLE [HumanResources].[Employee]120ALTER TABLE [HumanResources].[Employee]
    121 CHECK CONSTRAINT [FK_Employee_Person_BusinessEntityID]121 CHECK CONSTRAINT [FK_Employee_Person_BusinessEntityID]
    122 122 
    123GO123GO
    124CREATE UNIQUE NONCLUSTERED INDEX [AK_Employee_LoginID]124CREATE UNIQUE NONCLUSTERED INDEX [AK_Employee_LoginID]
    125 ON [HumanResources].[Employee] ([LoginID])125 ON [HumanResources].[Employee] ([LoginID])
    126 ON [PRIMARY]126 ON [PRIMARY]
    127GO127GO
    128CREATE UNIQUE NONCLUSTERED INDEX [AK_Employee_NationalIDNumber]128CREATE UNIQUE NONCLUSTERED INDEX [AK_Employee_NationalIDNumber]
    129 ON [HumanResources].[Employee] ([NationalIDNumber])129 ON [HumanResources].[Employee] ([NationalIDNumber])
    130 ON [PRIMARY]130 ON [PRIMARY]
    131GO131GO
    132CREATE UNIQUE NONCLUSTERED INDEX [AK_Employee_rowguid]132CREATE UNIQUE NONCLUSTERED INDEX [AK_Employee_rowguid]
    133 ON [HumanResources].[Employee] ([rowguid])133 ON [HumanResources].[Employee] ([rowguid])
    134 ON [PRIMARY]134 ON [PRIMARY]
    135GO135GO
    136CREATE NONCLUSTERED INDEX [IX_Employee_OrganizationLevel_OrganizationNode]136CREATE NONCLUSTERED INDEX [IX_Employee_OrganizationLevel_OrganizationNode]
    137 ON [HumanResources].[Employee] ([OrganizationLevel], [OrganizationNode])137 ON [HumanResources].[Employee] ([OrganizationLevel], [OrganizationNode])
    138 ON [PRIMARY]138 ON [PRIMARY]
    139GO139GO
    140CREATE NONCLUSTERED INDEX [IX_Employee_OrganizationNode]140CREATE NONCLUSTERED INDEX [IX_Employee_OrganizationNode]
    141 ON [HumanResources].[Employee] ([OrganizationNode])141 ON [HumanResources].[Employee] ([OrganizationNode])
    142 ON [PRIMARY]142 ON [PRIMARY]
    143GO143GO
    144ALTER TABLE [HumanResources].[Employee] SET (LOCK_ESCALATION = TABLE)144ALTER TABLE [HumanResources].[Employee] SET (LOCK_ESCALATION = TABLE)
    145GO145GO
    146 146 

    Table //[Production].[Culture] (back to Summary)
    Source projectTarget database
      1SET ANSI_NULLS ON
      2SET QUOTED_IDENTIFIER ON
      3
      4GO
      5CREATE TABLE [Production].[Culture] (
      6 [CultureID] [nchar](6) NOT NULL,
      7 [Name] [dbo].[Name] NOT NULL,
      8 [ModifiedDate] [datetime] NOT NULL
      9) ON [PRIMARY]
      10GO
      11ALTER TABLE [Production].[Culture]
      12 ADD
      13 CONSTRAINT [PK_Culture_CultureID]
      14 PRIMARY KEY
      15 CLUSTERED
      16 ([CultureID])
      17 ON [PRIMARY]
      18GO
      19ALTER TABLE [Production].[Culture]
      20 ADD
      21 CONSTRAINT [DF_Culture_ModifiedDate]
      22 DEFAULT (getdate()) FOR [ModifiedDate]
      23GO
      24CREATE UNIQUE NONCLUSTERED INDEX [AK_Culture_Name]
      25 ON [Production].[Culture] ([Name])
      26 ON [PRIMARY]
      27GO
      28ALTER TABLE [Production].[Culture] SET (LOCK_ESCALATION = TABLE)
      29GO
      30

    Table [Production].[ProductModelProductDescriptionCulture]//[Production].[ProductModelProductDescriptionCulture] (back to Summary)
    Source projectTarget database
    1SET ANSI_NULLS ON1SET ANSI_NULLS ON
    2SET QUOTED_IDENTIFIER ON2SET QUOTED_IDENTIFIER ON
    33
    4GO4GO
    5CREATE TABLE [Production].[ProductModelProductDescriptionCulture] (5CREATE TABLE [Production].[ProductModelProductDescriptionCulture] (
    6 [ProductModelID] [int] NOT NULL,6 [ProductModelID] [int] NOT NULL,
    7 [ProductDescriptionID] [int] NOT NULL,7 [ProductDescriptionID] [int] NOT NULL,
      8 [CultureID] [nchar](6) NOT NULL,
    8 [ModifiedDate] [datetime] NOT NULL9 [ModifiedDate] [datetime] NOT NULL
    9) ON [PRIMARY]10) ON [PRIMARY]
    10GO11GO
    11ALTER TABLE [Production].[ProductModelProductDescriptionCulture]12ALTER TABLE [Production].[ProductModelProductDescriptionCulture]
    12 ADD13 ADD
    13 CONSTRAINT [PK_ProductModelProductDescriptionCulture_ProductModelID_ProductDescriptionID_CultureID]14 CONSTRAINT [PK_ProductModelProductDescriptionCulture_ProductModelID_ProductDescriptionID_CultureID]
    14 PRIMARY KEY15 PRIMARY KEY
    15 CLUSTERED16 CLUSTERED
    16 ([ProductModelID], [ProductDescriptionID])17 ([ProductModelID], [ProductDescriptionID], [CultureID])
    17 ON [PRIMARY]18 ON [PRIMARY]
    18GO19GO
    19ALTER TABLE [Production].[ProductModelProductDescriptionCulture]20ALTER TABLE [Production].[ProductModelProductDescriptionCulture]
    20 ADD21 ADD
    21 CONSTRAINT [DF_ProductModelProductDescriptionCulture_ModifiedDate]22 CONSTRAINT [DF_ProductModelProductDescriptionCulture_ModifiedDate]
      23 DEFAULT (getdate()) FOR [ModifiedDate]
      24GO
      25ALTER TABLE [Production].[ProductModelProductDescriptionCulture]
      26 WITH CHECK
      27 ADD CONSTRAINT [FK_ProductModelProductDescriptionCulture_Culture_CultureID]
      28 FOREIGN KEY ([CultureID]) REFERENCES [Production].[Culture] ([CultureID])
      29ALTER TABLE [Production].[ProductModelProductDescriptionCulture]
      30 CHECK CONSTRAINT [FK_ProductModelProductDescriptionCulture_Culture_CultureID]
    22 DEFAULT (getdate()) FOR [ModifiedDate]31 
    23GO32GO
    24ALTER TABLE [Production].[ProductModelProductDescriptionCulture]33ALTER TABLE [Production].[ProductModelProductDescriptionCulture]
    25 WITH CHECK34 WITH CHECK
    26 ADD CONSTRAINT [FK_ProductModelProductDescriptionCulture_ProductDescription_ProductDescriptionID]35 ADD CONSTRAINT [FK_ProductModelProductDescriptionCulture_ProductDescription_ProductDescriptionID]
    27 FOREIGN KEY ([ProductDescriptionID]) REFERENCES [Production].[ProductDescription] ([ProductDescriptionID])36 FOREIGN KEY ([ProductDescriptionID]) REFERENCES [Production].[ProductDescription] ([ProductDescriptionID])
    28ALTER TABLE [Production].[ProductModelProductDescriptionCulture]37ALTER TABLE [Production].[ProductModelProductDescriptionCulture]
    29 CHECK CONSTRAINT [FK_ProductModelProductDescriptionCulture_ProductDescription_ProductDescriptionID]38 CHECK CONSTRAINT [FK_ProductModelProductDescriptionCulture_ProductDescription_ProductDescriptionID]
    30 39 
    31GO40GO
    32ALTER TABLE [Production].[ProductModelProductDescriptionCulture]41ALTER TABLE [Production].[ProductModelProductDescriptionCulture]
    33 WITH CHECK42 WITH CHECK
    34 ADD CONSTRAINT [FK_ProductModelProductDescriptionCulture_ProductModel_ProductModelID]43 ADD CONSTRAINT [FK_ProductModelProductDescriptionCulture_ProductModel_ProductModelID]
    35 FOREIGN KEY ([ProductModelID]) REFERENCES [Production].[ProductModel] ([ProductModelID])44 FOREIGN KEY ([ProductModelID]) REFERENCES [Production].[ProductModel] ([ProductModelID])
    36ALTER TABLE [Production].[ProductModelProductDescriptionCulture]45ALTER TABLE [Production].[ProductModelProductDescriptionCulture]
    37 CHECK CONSTRAINT [FK_ProductModelProductDescriptionCulture_ProductModel_ProductModelID]46 CHECK CONSTRAINT [FK_ProductModelProductDescriptionCulture_ProductModel_ProductModelID]
    38 47 
    39GO48GO
    40ALTER TABLE [Production].[ProductModelProductDescriptionCulture] SET (LOCK_ESCALATION = TABLE)49ALTER TABLE [Production].[ProductModelProductDescriptionCulture] SET (LOCK_ESCALATION = TABLE)
    41GO50GO
    42 51 

    View [Production].[vProductAndDescription]//[Production].[vProductAndDescription] (back to Summary)
    Source projectTarget database
    1SET ANSI_NULLS ON1SET ANSI_NULLS ON
    2SET QUOTED_IDENTIFIER ON2SET QUOTED_IDENTIFIER ON
    3GO3GO
    4CREATE VIEW [Production].[vProductAndDescription]4CREATE VIEW [Production].[vProductAndDescription]
    5WITH SCHEMABINDING5WITH SCHEMABINDING
    6AS6AS
    7-- View (indexed or standard) to display products and product descriptions by language.7-- View (indexed or standard) to display products and product descriptions by language.
    8SELECT8SELECT
    9p.[ProductID]9p.[ProductID]
    10,p.[Name]10,p.[Name]
    11,pm.[Name] AS [ProductModel]11,pm.[Name] AS [ProductModel]
      12,pmx.[CultureID]
    12,pd.[Description]13,pd.[Description]
    13FROM [Production].[Product] p14FROM [Production].[Product] p
    14INNER JOIN [Production].[ProductModel] pm15INNER JOIN [Production].[ProductModel] pm
    15ON p.[ProductModelID] = pm.[ProductModelID]16ON p.[ProductModelID] = pm.[ProductModelID]
    16INNER JOIN [Production].[ProductModelProductDescriptionCulture] pmx17INNER JOIN [Production].[ProductModelProductDescriptionCulture] pmx
    17ON pm.[ProductModelID] = pmx.[ProductModelID]18ON pm.[ProductModelID] = pmx.[ProductModelID]
    18INNER JOIN [Production].[ProductDescription] pd19INNER JOIN [Production].[ProductDescription] pd
      20ON pmx.[ProductDescriptionID] = pd.[ProductDescriptionID];
      21GO
      22CREATE UNIQUE CLUSTERED INDEX [IX_vProductAndDescription]
      23 ON [Production].[vProductAndDescription] ([CultureID], [ProductID])
    19ON pmx.[ProductDescriptionID] = pd.[ProductDescriptionID];24 ON [PRIMARY]
    20GO25GO
    21 26 

    Copyright © 2013 ReadyRoll