Advertisement
brunobola

Untitled

May 12th, 2023
88
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 12.43 KB | None | 0 0
  1. /*
  2. Script created by SQL Examiner 7.1.8.338 at 12-05-2023 16:31:01.
  3. Run this script on [192.168.0.4,1983].PRITPB to make it the same as PRIMAVERAV10.PRITPB
  4. */
  5. USE [PRITPB]
  6. GO
  7. SET NOCOUNT ON
  8. SET NOEXEC OFF
  9. SET ARITHABORT ON
  10. SET XACT_ABORT ON
  11. SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
  12. GO
  13. BEGIN TRAN
  14. GO
  15. --step 1: add columns to Table dbo.Departamentos----------------------------------------------------
  16. ALTER TABLE [dbo].[Departamentos] ADD
  17.     [CDU_UnidadeNegocio]    [nvarchar](10) COLLATE Latin1_General_CI_AS NULL
  18. GO
  19. IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 1 is completed with errors' ROLLBACK TRAN END
  20. GO
  21. IF @@TRANCOUNT = 0 BEGIN PRINT 'step 1 is completed with errors' SET NOEXEC ON END
  22. GO
  23. --step 2: dbo.TDU_UnidadeNegocio: drop default TDU_UnidadeNegocio_CDU_Descricao_DF------------------
  24. IF EXISTS (SELECT * FROM sys.default_constraints WHERE object_id = OBJECT_ID(N'[dbo].[TDU_UnidadeNegocio_CDU_Descricao_DF]') AND parent_object_id = OBJECT_ID(N'[dbo].[TDU_UnidadeNegocio]'))
  25. ALTER TABLE [dbo].[TDU_UnidadeNegocio] DROP CONSTRAINT [TDU_UnidadeNegocio_CDU_Descricao_DF]
  26. GO
  27. IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 2 is completed with errors' ROLLBACK TRAN END
  28. GO
  29. IF @@TRANCOUNT = 0 BEGIN PRINT 'step 2 is completed with errors' SET NOEXEC ON END
  30. GO
  31. --step 3: dbo.TDU_UnidadeNegocio: drop default DF_TDU_UnidadeNegocio_DataInsercao-------------------
  32. IF EXISTS (SELECT * FROM sys.default_constraints WHERE object_id = OBJECT_ID(N'[dbo].[DF_TDU_UnidadeNegocio_DataInsercao]') AND parent_object_id = OBJECT_ID(N'[dbo].[TDU_UnidadeNegocio]'))
  33. ALTER TABLE [dbo].[TDU_UnidadeNegocio] DROP CONSTRAINT [DF_TDU_UnidadeNegocio_DataInsercao]
  34. GO
  35. IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 3 is completed with errors' ROLLBACK TRAN END
  36. GO
  37. IF @@TRANCOUNT = 0 BEGIN PRINT 'step 3 is completed with errors' SET NOEXEC ON END
  38. GO
  39. --step 4: dbo.TDU_UnidadeNegocio: drop default DF_TDU_UnidadeNegocio_DataAtualizacao----------------
  40. IF EXISTS (SELECT * FROM sys.default_constraints WHERE object_id = OBJECT_ID(N'[dbo].[DF_TDU_UnidadeNegocio_DataAtualizacao]') AND parent_object_id = OBJECT_ID(N'[dbo].[TDU_UnidadeNegocio]'))
  41. ALTER TABLE [dbo].[TDU_UnidadeNegocio] DROP CONSTRAINT [DF_TDU_UnidadeNegocio_DataAtualizacao]
  42. GO
  43. IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 4 is completed with errors' ROLLBACK TRAN END
  44. GO
  45. IF @@TRANCOUNT = 0 BEGIN PRINT 'step 4 is completed with errors' SET NOEXEC ON END
  46. GO
  47. --step 5: create temp table tmp_TDU_UnidadeNegocio--------------------------------------------------
  48. CREATE TABLE [dbo].[tmp_TDU_UnidadeNegocio] (
  49.     [CDU_Codigo]                    [nvarchar](10)   COLLATE Latin1_General_CI_AS NOT NULL,
  50.     [CDU_Descricao]                 [nvarchar](100)  COLLATE Latin1_General_CI_AS NULL CONSTRAINT [TDU_UnidadeNegocio_CDU_Descricao_DF] DEFAULT ('Descrição'),
  51.     [CDU_Prefixo]                   [nvarchar](2)    COLLATE Latin1_General_CI_AS NOT NULL,
  52.     [CDU_Observacoes]               [nvarchar](512)  COLLATE Latin1_General_CI_AS NULL,
  53.     [CDU_PrefixoSugestao]           [nvarchar](2)    COLLATE Latin1_General_CI_AS NULL,
  54.     [CDU_TamanhoCampoSugestao]      [INT]            NULL,
  55.     [CDU_UsarSIGE]                  [bit]            NULL,
  56.     [CDU_PV]                        [bit]            NULL,
  57.     [CDU_CC]                        [bit]            NULL,
  58.     [DataInsercao]                  [datetime]       NULL CONSTRAINT [DF_TDU_UnidadeNegocio_DataInsercao] DEFAULT (getdate()),
  59.     [UtilizadorInsercao]            [INT]            NULL,
  60.     [DataAtualizacao]               [datetime]       NULL CONSTRAINT [DF_TDU_UnidadeNegocio_DataAtualizacao] DEFAULT (getdate()),
  61.     [UtilizadorAtualizacao]         [INT]            NULL
  62. ) ON [PRIMARY]
  63. GO
  64. IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 5 is completed with errors' ROLLBACK TRAN END
  65. GO
  66. IF @@TRANCOUNT = 0 BEGIN PRINT 'step 5 is completed with errors' SET NOEXEC ON END
  67. GO
  68. --step 6: copy existing data into new table tmp_TDU_UnidadeNegocio----------------------------------
  69. INSERT INTO [dbo].[tmp_TDU_UnidadeNegocio]([CDU_Codigo], [CDU_Descricao], [CDU_Prefixo], [CDU_Observacoes], [CDU_PrefixoSugestao], [CDU_TamanhoCampoSugestao], [CDU_UsarSIGE], [CDU_PV], [CDU_CC], [DataInsercao], [UtilizadorInsercao], [DataAtualizacao], [UtilizadorAtualizacao]) SELECT
  70.     [CDU_Codigo],
  71.     [CDU_Descricao],
  72.     [CDU_Prefixo],
  73.     [CDU_Observacoes],
  74.     [CDU_PrefixoSugestao],
  75.     [CDU_TamanhoCampoSugestao],
  76.     [CDU_UsarSIGE],
  77.     NULL,
  78.     NULL,
  79.     [DataInsercao],
  80.     [UtilizadorInsercao],
  81.     [DataAtualizacao],
  82.     [UtilizadorAtualizacao]
  83. FROM [dbo].[TDU_UnidadeNegocio]
  84. GO
  85. IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 6 is completed with errors' ROLLBACK TRAN END
  86. GO
  87. IF @@TRANCOUNT = 0 BEGIN PRINT 'step 6 is completed with errors' SET NOEXEC ON END
  88. GO
  89. --step 7: dbo.Artigo: drop foreign key FK_Artigo_TDU_UnidadeNegocio---------------------------------
  90. IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Artigo_TDU_UnidadeNegocio]') AND parent_object_id = OBJECT_ID(N'[dbo].[Artigo]'))
  91. ALTER TABLE [dbo].[Artigo] DROP CONSTRAINT [FK_Artigo_TDU_UnidadeNegocio]
  92. GO
  93. IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 7 is completed with errors' ROLLBACK TRAN END
  94. GO
  95. IF @@TRANCOUNT = 0 BEGIN PRINT 'step 7 is completed with errors' SET NOEXEC ON END
  96. GO
  97. --step 8: dbo.COP_Obras: drop foreign key FK_COP_Obras_TDU_UnidadeNegocio---------------------------
  98. IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_COP_Obras_TDU_UnidadeNegocio]') AND parent_object_id = OBJECT_ID(N'[dbo].[COP_Obras]'))
  99. ALTER TABLE [dbo].[COP_Obras] DROP CONSTRAINT [FK_COP_Obras_TDU_UnidadeNegocio]
  100. GO
  101. IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 8 is completed with errors' ROLLBACK TRAN END
  102. GO
  103. IF @@TRANCOUNT = 0 BEGIN PRINT 'step 8 is completed with errors' SET NOEXEC ON END
  104. GO
  105. --step 9: dbo.TDU_Obras: drop foreign key FK_TDU_Obras_TDU_UnidadeNegocio---------------------------
  106. IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_TDU_Obras_TDU_UnidadeNegocio]') AND parent_object_id = OBJECT_ID(N'[dbo].[TDU_Obras]'))
  107. ALTER TABLE [dbo].[TDU_Obras] DROP CONSTRAINT [FK_TDU_Obras_TDU_UnidadeNegocio]
  108. GO
  109. IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 9 is completed with errors' ROLLBACK TRAN END
  110. GO
  111. IF @@TRANCOUNT = 0 BEGIN PRINT 'step 9 is completed with errors' SET NOEXEC ON END
  112. GO
  113. --step 10: drop table dbo.TDU_UnidadeNegocio--------------------------------------------------------
  114. IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TDU_UnidadeNegocio]') AND TYPE IN (N'U'))
  115. DROP TABLE [dbo].[TDU_UnidadeNegocio]
  116. GO
  117. IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 10 is completed with errors' ROLLBACK TRAN END
  118. GO
  119. IF @@TRANCOUNT = 0 BEGIN PRINT 'step 10 is completed with errors' SET NOEXEC ON END
  120. GO
  121. --step 11: rename tmp_TDU_UnidadeNegocio to dbo.TDU_UnidadeNegocio----------------------------------
  122. DECLARE @i INT
  123. EXEC @i = sp_rename N'[dbo].[tmp_TDU_UnidadeNegocio]', N'TDU_UnidadeNegocio'
  124. IF @i <> 0 BEGIN PRINT 'step 11 is completed with errors' ROLLBACK TRAN END
  125. GO
  126. IF @@TRANCOUNT = 0 BEGIN PRINT 'step 11 is completed with errors' SET NOEXEC ON END
  127. GO
  128. --step 12: dbo.TDU_UnidadeNegocio: add primary key PK_TDU_UnidadeNegocio----------------------------
  129. IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[TDU_UnidadeNegocio]') AND name = N'PK_TDU_UnidadeNegocio')
  130. ALTER TABLE [dbo].[TDU_UnidadeNegocio] ADD CONSTRAINT [PK_TDU_UnidadeNegocio] PRIMARY KEY CLUSTERED ([CDU_Codigo])
  131. GO
  132. IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 12 is completed with errors' ROLLBACK TRAN END
  133. GO
  134. IF @@TRANCOUNT = 0 BEGIN PRINT 'step 12 is completed with errors' SET NOEXEC ON END
  135. GO
  136. --step 13: dbo.Departamentos: add foreign key FK_Departamentos_TDU_UnidadeNegocio-------------------
  137. IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Departamentos_TDU_UnidadeNegocio]') AND parent_object_id = OBJECT_ID(N'[dbo].[Departamentos]'))
  138. ALTER TABLE [dbo].[Departamentos] ADD CONSTRAINT [FK_Departamentos_TDU_UnidadeNegocio] FOREIGN KEY ([CDU_UnidadeNegocio]) REFERENCES [dbo].[TDU_UnidadeNegocio] ([CDU_Codigo])
  139. GO
  140. IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 13 is completed with errors' ROLLBACK TRAN END
  141. GO
  142. IF @@TRANCOUNT = 0 BEGIN PRINT 'step 13 is completed with errors' SET NOEXEC ON END
  143. GO
  144. --step 14: add columns to Table dbo.Zonas-----------------------------------------------------------
  145. ALTER TABLE [dbo].[Zonas] ADD
  146.     [CDU_PAV]   [bit] NULL,
  147.     [CDU_UnidadeNegocio]    [nvarchar](10) COLLATE Latin1_General_CI_AS NULL
  148. GO
  149. IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 14 is completed with errors' ROLLBACK TRAN END
  150. GO
  151. IF @@TRANCOUNT = 0 BEGIN PRINT 'step 14 is completed with errors' SET NOEXEC ON END
  152. GO
  153. --step 15: dbo.Zonas: add foreign key FK_Zonas_TDU_UnidadeNegocio-----------------------------------
  154. IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Zonas_TDU_UnidadeNegocio]') AND parent_object_id = OBJECT_ID(N'[dbo].[Zonas]'))
  155. ALTER TABLE [dbo].[Zonas] ADD CONSTRAINT [FK_Zonas_TDU_UnidadeNegocio] FOREIGN KEY ([CDU_UnidadeNegocio]) REFERENCES [dbo].[TDU_UnidadeNegocio] ([CDU_Codigo])
  156. GO
  157. IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 15 is completed with errors' ROLLBACK TRAN END
  158. GO
  159. IF @@TRANCOUNT = 0 BEGIN PRINT 'step 15 is completed with errors' SET NOEXEC ON END
  160. GO
  161. --step 16: dbo.TDU_UnidadeNegocio: add foreign key FK_TDU_UnidadeNegocio_Users1---------------------
  162. IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_TDU_UnidadeNegocio_Users1]') AND parent_object_id = OBJECT_ID(N'[dbo].[TDU_UnidadeNegocio]'))
  163. ALTER TABLE [dbo].[TDU_UnidadeNegocio] ADD CONSTRAINT [FK_TDU_UnidadeNegocio_Users1] FOREIGN KEY ([UtilizadorAtualizacao]) REFERENCES [dbo].[Users] ([UserId])
  164. GO
  165. IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 16 is completed with errors' ROLLBACK TRAN END
  166. GO
  167. IF @@TRANCOUNT = 0 BEGIN PRINT 'step 16 is completed with errors' SET NOEXEC ON END
  168. GO
  169. --step 17: dbo.TDU_UnidadeNegocio: add foreign key FK_TDU_UnidadeNegocio_Users----------------------
  170. IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_TDU_UnidadeNegocio_Users]') AND parent_object_id = OBJECT_ID(N'[dbo].[TDU_UnidadeNegocio]'))
  171. ALTER TABLE [dbo].[TDU_UnidadeNegocio] ADD CONSTRAINT [FK_TDU_UnidadeNegocio_Users] FOREIGN KEY ([UtilizadorInsercao]) REFERENCES [dbo].[Users] ([UserId])
  172. GO
  173. IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 17 is completed with errors' ROLLBACK TRAN END
  174. GO
  175. IF @@TRANCOUNT = 0 BEGIN PRINT 'step 17 is completed with errors' SET NOEXEC ON END
  176. GO
  177. --step 18: dbo.TDU_Obras: add foreign key FK_TDU_Obras_TDU_UnidadeNegocio---------------------------
  178. IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_TDU_Obras_TDU_UnidadeNegocio]') AND parent_object_id = OBJECT_ID(N'[dbo].[TDU_Obras]'))
  179. ALTER TABLE [dbo].[TDU_Obras] ADD CONSTRAINT [FK_TDU_Obras_TDU_UnidadeNegocio] FOREIGN KEY ([CDU_UnidadeNegocio]) REFERENCES [dbo].[TDU_UnidadeNegocio] ([CDU_Codigo])
  180. GO
  181. IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 18 is completed with errors' ROLLBACK TRAN END
  182. GO
  183. IF @@TRANCOUNT = 0 BEGIN PRINT 'step 18 is completed with errors' SET NOEXEC ON END
  184. GO
  185. --step 19: dbo.COP_Obras: add foreign key FK_COP_Obras_TDU_UnidadeNegocio---------------------------
  186. IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_COP_Obras_TDU_UnidadeNegocio]') AND parent_object_id = OBJECT_ID(N'[dbo].[COP_Obras]'))
  187. ALTER TABLE [dbo].[COP_Obras] ADD CONSTRAINT [FK_COP_Obras_TDU_UnidadeNegocio] FOREIGN KEY ([CDU_UnidadeNegocio]) REFERENCES [dbo].[TDU_UnidadeNegocio] ([CDU_Codigo])
  188. GO
  189. IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 19 is completed with errors' ROLLBACK TRAN END
  190. GO
  191. IF @@TRANCOUNT = 0 BEGIN PRINT 'step 19 is completed with errors' SET NOEXEC ON END
  192. GO
  193. --step 20: dbo.Artigo: add foreign key FK_Artigo_TDU_UnidadeNegocio---------------------------------
  194. IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Artigo_TDU_UnidadeNegocio]') AND parent_object_id = OBJECT_ID(N'[dbo].[Artigo]'))
  195. ALTER TABLE [dbo].[Artigo] ADD CONSTRAINT [FK_Artigo_TDU_UnidadeNegocio] FOREIGN KEY ([CDU_UnidadeNegocio]) REFERENCES [dbo].[TDU_UnidadeNegocio] ([CDU_Codigo])
  196. GO
  197. IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 20 is completed with errors' ROLLBACK TRAN END
  198. GO
  199. IF @@TRANCOUNT = 0 BEGIN PRINT 'step 20 is completed with errors' SET NOEXEC ON END
  200. GO
  201. ----------------------------------------------------------------------
  202. IF @@TRANCOUNT > 0 BEGIN COMMIT TRAN PRINT 'Synchronization is successfully completed.' END
  203. GO
  204. SET NOEXEC OFF
  205. GO
  206.  
  207.  
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement