API tools faq
paste
Advertisement
brunobola

Untitled

brunobola
May 12th, 2023
92
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 12.43 KB | None | 0 0
raw download clone embed print report
  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
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!