LarsFosdal

ApexSQL Diff script

Jan 16th, 2015
102
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. /* ------------------------------------------------------------
  2.  
  3. Note: Requires a SQL Server database named PSDEtherDevelLars
  4.  
  5. DESCRIPTION: Schema Synchronization Script for Object(s) \r\n
  6.     views:
  7.         [dbo].[v_xml_logs]
  8.  
  9.     procedures:
  10.         [dbo].[p_delete_xml_logs], [dbo].[p_upsert_xml_logs]
  11.  
  12.     tables:
  13.         [dbo].[t_xml_logs]
  14.  
  15.      Make PSDEtherDevelLars Equal PSDDevelLars
  16.  
  17.    AUTHOR:  [Insert author name]
  18.  
  19.    DATE:    16-Jan-15 10:47:06
  20.    
  21.    ------------------------------------------------------------ */
  22.  
  23. SET NOEXEC OFF
  24. SET ANSI_WARNINGS ON
  25. SET XACT_ABORT ON
  26. SET IMPLICIT_TRANSACTIONS OFF
  27. SET ARITHABORT ON
  28. SET NOCOUNT ON
  29. SET QUOTED_IDENTIFIER ON
  30. SET NUMERIC_ROUNDABORT OFF
  31. SET CONCAT_NULL_YIELDS_NULL ON
  32. SET ANSI_NULLS ON
  33. SET ANSI_PADDING ON
  34. GO
  35. USE [PSDEtherDevelLars]
  36. GO
  37.  
  38. BEGIN TRAN
  39. GO
  40.  
  41. SET ANSI_NULLS ON
  42. SET QUOTED_IDENTIFIER ON
  43.  
  44. GO
  45.  
  46. IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN IF @@TRANCOUNT>0 ROLLBACK SET NOEXEC ON END
  47. GO
  48. -- Create Table [dbo].[t_xml_logs]
  49. Print 'Create Table [dbo].[t_xml_logs]'
  50. GO
  51. CREATE TABLE [dbo].[t_xml_logs] (
  52.         [Id]            [int] IDENTITY(1, 1) NOT NULL,
  53.         [TimeStamp]     [datetime] NULL,
  54.         [XType]         [smallint] NULL,
  55.         [XLevel]        [smallint] NULL,
  56.         [Title]         [varchar](500) COLLATE Danish_Norwegian_CI_AS NULL,
  57.         [ClientId]      [int] NULL,
  58.         [XMLDoc]        [xml] NULL,
  59.         [XCategory]     [smallint] NULL,
  60.         [ProcessID]     [int] NULL,
  61.         [ThreadID]      [int] NULL
  62. )
  63. GO
  64.  
  65. IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN IF @@TRANCOUNT>0 ROLLBACK SET NOEXEC ON END
  66. GO
  67. -- Create Index IX_xml_logs_ClientId on [dbo].[t_xml_logs]
  68. Print 'Create Index IX_xml_logs_ClientId on [dbo].[t_xml_logs]'
  69. GO
  70. CREATE NONCLUSTERED INDEX [IX_xml_logs_ClientId]
  71.     ON [dbo].[t_xml_logs] ([ClientId])
  72. GO
  73.  
  74. IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN IF @@TRANCOUNT>0 ROLLBACK SET NOEXEC ON END
  75. GO
  76. -- Create Index IX_xml_logs_id on [dbo].[t_xml_logs]
  77. Print 'Create Index IX_xml_logs_id on [dbo].[t_xml_logs]'
  78. GO
  79. CREATE UNIQUE CLUSTERED INDEX [IX_xml_logs_id]
  80.     ON [dbo].[t_xml_logs] ([Id])
  81. GO
  82.  
  83. IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN IF @@TRANCOUNT>0 ROLLBACK SET NOEXEC ON END
  84. GO
  85. -- Create Index IX_xml_logs_xlevel on [dbo].[t_xml_logs]
  86. Print 'Create Index IX_xml_logs_xlevel on [dbo].[t_xml_logs]'
  87. GO
  88. CREATE NONCLUSTERED INDEX [IX_xml_logs_xlevel]
  89.     ON [dbo].[t_xml_logs] ([XLevel], [Id], [TimeStamp])
  90. GO
  91.  
  92. IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN IF @@TRANCOUNT>0 ROLLBACK SET NOEXEC ON END
  93. GO
  94. -- Create Index timeorder on [dbo].[t_xml_logs]
  95. Print 'Create Index timeorder on [dbo].[t_xml_logs]'
  96. GO
  97. CREATE NONCLUSTERED INDEX [timeorder]
  98.     ON [dbo].[t_xml_logs] ([TimeStamp] DESC, [Id] DESC)
  99. GO
  100.  
  101. IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN IF @@TRANCOUNT>0 ROLLBACK SET NOEXEC ON END
  102. GO
  103. ALTER TABLE [dbo].[t_xml_logs] SET (LOCK_ESCALATION = TABLE)
  104. GO
  105.  
  106. IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN IF @@TRANCOUNT>0 ROLLBACK SET NOEXEC ON END
  107. GO
  108. -- Create View [dbo].[v_xml_logs]
  109. Print 'Create View [dbo].[v_xml_logs]'
  110. GO
  111. SET ANSI_NULLS ON
  112. SET QUOTED_IDENTIFIER ON
  113. GO
  114.  
  115. IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN IF @@TRANCOUNT>0 ROLLBACK SET NOEXEC ON END
  116. GO
  117. CREATE VIEW dbo.v_xml_logs
  118. AS
  119. SELECT        Id, TimeStamp, XType, XLevel, Title, ClientId, XMLDoc, XCategory, ProcessID, ThreadID
  120. FROM            dbo.t_xml_logs
  121. GO
  122.  
  123. IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN IF @@TRANCOUNT>0 ROLLBACK SET NOEXEC ON END
  124. GO
  125. -- Create Extended Property MS_DiagramPane1 on [dbo].[v_xml_logs]
  126. Print 'Create Extended Property MS_DiagramPane1 on [dbo].[v_xml_logs]'
  127. GO
  128. EXEC sp_addextendedproperty N'MS_DiagramPane1', N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
  129. Begin DesignProperties =
  130.   Begin PaneConfigurations =
  131.      Begin PaneConfiguration = 0
  132.         NumPanes = 4
  133.         Configuration = "(H (1[32] 4[29] 2[20] 3) )"
  134.      End
  135.      Begin PaneConfiguration = 1
  136.         NumPanes = 3
  137.         Configuration = "(H (1 [50] 4 [25] 3))"
  138.      End
  139.      Begin PaneConfiguration = 2
  140.         NumPanes = 3
  141.         Configuration = "(H (1 [50] 2 [25] 3))"
  142.      End
  143.      Begin PaneConfiguration = 3
  144.         NumPanes = 3
  145.         Configuration = "(H (4 [30] 2 [40] 3))"
  146.      End
  147.      Begin PaneConfiguration = 4
  148.         NumPanes = 2
  149.         Configuration = "(H (1 [56] 3))"
  150.      End
  151.      Begin PaneConfiguration = 5
  152.         NumPanes = 2
  153.         Configuration = "(H (2 [66] 3))"
  154.      End
  155.      Begin PaneConfiguration = 6
  156.         NumPanes = 2
  157.         Configuration = "(H (4 [50] 3))"
  158.      End
  159.      Begin PaneConfiguration = 7
  160.         NumPanes = 1
  161.         Configuration = "(V (3))"
  162.      End
  163.      Begin PaneConfiguration = 8
  164.         NumPanes = 3
  165.         Configuration = "(H (1[56] 4[18] 2) )"
  166.      End
  167.      Begin PaneConfiguration = 9
  168.         NumPanes = 2
  169.         Configuration = "(H (1 [75] 4))"
  170.      End
  171.      Begin PaneConfiguration = 10
  172.         NumPanes = 2
  173.         Configuration = "(H (1[66] 2) )"
  174.      End
  175.      Begin PaneConfiguration = 11
  176.         NumPanes = 2
  177.         Configuration = "(H (4 [60] 2))"
  178.      End
  179.      Begin PaneConfiguration = 12
  180.         NumPanes = 1
  181.         Configuration = "(H (1) )"
  182.      End
  183.      Begin PaneConfiguration = 13
  184.         NumPanes = 1
  185.         Configuration = "(V (4))"
  186.      End
  187.      Begin PaneConfiguration = 14
  188.         NumPanes = 1
  189.         Configuration = "(V (2))"
  190.      End
  191.      ActivePaneConfig = 0
  192.   End
  193.   Begin DiagramPane =
  194.      Begin Origin =
  195.         Top = 0
  196.         Left = 0
  197.      End
  198.      Begin Tables =
  199.         Begin Table = "t_xml_logs"
  200.            Begin Extent =
  201.               Top = 6
  202.               Left = 38
  203.               Bottom = 300
  204.               Right = 198
  205.            End
  206.            DisplayFlags = 280
  207.            TopColumn = 0
  208.         End
  209.      End
  210.   End
  211.   Begin SQLPane =
  212.   End
  213.   Begin DataPane =
  214.      Begin ParameterDefaults = ""
  215.      End
  216.   End
  217.   Begin CriteriaPane =
  218.      Begin ColumnWidths = 11
  219.         Column = 1440
  220.         Alias = 900
  221.         Table = 1170
  222.         Output = 720
  223.         Append = 1400
  224.         NewValue = 1170
  225.         SortType = 1350
  226.         SortOrder = 1410
  227.         GroupBy = 1350
  228.         Filter = 1350
  229.         Or = 1350
  230.         Or = 1350
  231.         Or = 1350
  232.      End
  233.   End
  234. End
  235. ', 'SCHEMA', N'dbo', 'VIEW', N'v_xml_logs', NULL, NULL
  236. GO
  237.  
  238. IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN IF @@TRANCOUNT>0 ROLLBACK SET NOEXEC ON END
  239. GO
  240. -- Create Extended Property MS_DiagramPaneCount on [dbo].[v_xml_logs]
  241. Print 'Create Extended Property MS_DiagramPaneCount on [dbo].[v_xml_logs]'
  242. GO
  243. EXEC sp_addextendedproperty N'MS_DiagramPaneCount', 1, 'SCHEMA', N'dbo', 'VIEW', N'v_xml_logs', NULL, NULL
  244. GO
  245.  
  246. IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN IF @@TRANCOUNT>0 ROLLBACK SET NOEXEC ON END
  247. GO
  248. SET ANSI_NULLS ON
  249. SET QUOTED_IDENTIFIER ON
  250. GO
  251.  
  252. IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN IF @@TRANCOUNT>0 ROLLBACK SET NOEXEC ON END
  253. GO
  254. -- Create Procedure [dbo].[p_upsert_xml_logs]
  255. Print 'Create Procedure [dbo].[p_upsert_xml_logs]'
  256. GO
  257. CREATE PROCEDURE [dbo].[p_upsert_xml_logs]
  258.   @ErrorMsg varchar(1000) OUTPUT,
  259.   @Id int OUTPUT,
  260.   @TimeStamp datetime = NULL,
  261.   @XType smallint,
  262.   @XLevel smallint,
  263.   @Title varchar(100),
  264.   @ClientId int,
  265.   @XMLDoc xml,
  266.   @XCategory smallint = 0,
  267.   @ProcessId int = NULL,
  268.   @ThreadId int = NULL
  269. WITH
  270. EXECUTE AS CALLER
  271. AS
  272. BEGIN
  273.    
  274.     DECLARE @aId int;
  275.     SET @ErrorMsg = '';
  276.     SET @aId = 0;
  277.  
  278.     BEGIN TRY
  279.         -- Selects the Id field to determine if INSERT og UPDATE are to be used
  280.         IF @Id Is Null
  281.           SET @aId = 0;
  282.         ELSE
  283.           -- @aId will be 0 if Id can't be found
  284.           SELECT @aId = Id FROM t_xml_logs WHERE Id=@Id;
  285.  
  286.         IF @aId > 0
  287.         BEGIN
  288.             UPDATE t_xml_logs SET
  289.                 TimeStamp=@TimeStamp,
  290.                 XType=@XType,
  291.                 XLevel=@XLevel,
  292.                 Title=@Title,
  293.                 ClientId=@ClientId,
  294.                 XMLDoc=@XMLDoc,
  295.                 XCategory=@XCategory,
  296.              ProcessId=@ProcessId,
  297.              ThreadId=@ThreadId
  298.             WHERE Id = @aId;
  299.             SELECT @Id = @aId; -- Return existing Id
  300.         END
  301.         ELSE
  302.         BEGIN
  303.             DECLARE @TmpIdTable table(ID int)
  304.             INSERT INTO t_xml_logs(
  305.                 TimeStamp,
  306.                 XType,
  307.                 XLevel,
  308.                 Title,
  309.                 ClientId,
  310.                 XMLDoc,
  311.                 XCategory,
  312.              ProcessId,
  313.              ThreadId
  314.             )
  315.             output inserted.ID into @TmpIdTable
  316.             VALUES (
  317.                 ISNULL(@TimeStamp, GETDATE()),
  318.                 @XType,
  319.                 @XLevel,
  320.                 @Title,
  321.                 @ClientId,
  322.                 @XMLDoc,
  323.                 @XCategory,
  324.              @ProcessId,
  325.              @ThreadId
  326.             );
  327.             SELECT TOP 1 @Id = ID  FROM @TmpIdTable
  328.         END
  329.     END TRY
  330.  
  331.     --Handling SQL Exceptions without raising errors
  332.     BEGIN CATCH
  333.         IF ISNULL(@Id, -1) = -1
  334.         BEGIN
  335.           SELECT @Id = -1;
  336.         END
  337.         SELECT @ErrorMsg = ERROR_MESSAGE();
  338.         RETURN SELECT ERROR_NUMBER();
  339.     END CATCH
  340.  
  341.     RETURN 0;
  342.  
  343. END
  344. GO
  345.  
  346. IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN IF @@TRANCOUNT>0 ROLLBACK SET NOEXEC ON END
  347. GO
  348. SET ANSI_NULLS ON
  349. SET QUOTED_IDENTIFIER ON
  350. GO
  351.  
  352. IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN IF @@TRANCOUNT>0 ROLLBACK SET NOEXEC ON END
  353. GO
  354. -- Create Procedure [dbo].[p_delete_xml_logs]
  355. Print 'Create Procedure [dbo].[p_delete_xml_logs]'
  356. GO
  357. CREATE PROCEDURE [dbo].[p_delete_xml_logs]
  358.     @ErrorMsg varchar(1000) output,
  359.     @Id int,
  360.     @TimeStamp datetime,
  361.     @Title varchar(100),
  362.     @ClientId int
  363. AS
  364. BEGIN
  365.  
  366.     SET @ErrorMsg = '';
  367.     BEGIN TRY
  368.         IF @Id Is Null
  369.             DELETE FROM t_xml_logs WHERE TimeStamp = @TimeStamp AND Title = @Title AND ClientId = @ClientId;
  370.         ELSE
  371.             DELETE FROM t_xml_logs WHERE Id = @Id;
  372.  
  373.         -- Return rows affected
  374.         RETURN SELECT @@ROWCOUNT;
  375.     END TRY
  376.  
  377.     --Handling SQL Exceptions without raising errors
  378.     BEGIN CATCH
  379.         SELECT @ErrorMsg = ERROR_MESSAGE();
  380.         RETURN SELECT ERROR_NUMBER();
  381.     END CATCH
  382. END
  383. GO
  384.  
  385. IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN IF @@TRANCOUNT>0 ROLLBACK SET NOEXEC ON END
  386. GO
  387.  
  388. IF @@TRANCOUNT>0
  389.     COMMIT
  390.  
  391. SET NOEXEC OFF
  392. GO
RAW Paste Data