Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- /*
- Script created by SQL Examiner 7.1.8.338 at 12-05-2023 16:31:01.
- Run this script on [192.168.0.4,1983].PRITPB to make it the same as PRIMAVERAV10.PRITPB
- */
- USE [PRITPB]
- GO
- SET NOCOUNT ON
- SET NOEXEC OFF
- SET ARITHABORT ON
- SET XACT_ABORT ON
- SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
- GO
- BEGIN TRAN
- GO
- --step 1: add columns to Table dbo.Departamentos----------------------------------------------------
- ALTER TABLE [dbo].[Departamentos] ADD
- [CDU_UnidadeNegocio] [nvarchar](10) COLLATE Latin1_General_CI_AS NULL
- GO
- IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 1 is completed with errors' ROLLBACK TRAN END
- GO
- IF @@TRANCOUNT = 0 BEGIN PRINT 'step 1 is completed with errors' SET NOEXEC ON END
- GO
- --step 2: dbo.TDU_UnidadeNegocio: drop default TDU_UnidadeNegocio_CDU_Descricao_DF------------------
- 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]'))
- ALTER TABLE [dbo].[TDU_UnidadeNegocio] DROP CONSTRAINT [TDU_UnidadeNegocio_CDU_Descricao_DF]
- GO
- IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 2 is completed with errors' ROLLBACK TRAN END
- GO
- IF @@TRANCOUNT = 0 BEGIN PRINT 'step 2 is completed with errors' SET NOEXEC ON END
- GO
- --step 3: dbo.TDU_UnidadeNegocio: drop default DF_TDU_UnidadeNegocio_DataInsercao-------------------
- 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]'))
- ALTER TABLE [dbo].[TDU_UnidadeNegocio] DROP CONSTRAINT [DF_TDU_UnidadeNegocio_DataInsercao]
- GO
- IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 3 is completed with errors' ROLLBACK TRAN END
- GO
- IF @@TRANCOUNT = 0 BEGIN PRINT 'step 3 is completed with errors' SET NOEXEC ON END
- GO
- --step 4: dbo.TDU_UnidadeNegocio: drop default DF_TDU_UnidadeNegocio_DataAtualizacao----------------
- 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]'))
- ALTER TABLE [dbo].[TDU_UnidadeNegocio] DROP CONSTRAINT [DF_TDU_UnidadeNegocio_DataAtualizacao]
- GO
- IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 4 is completed with errors' ROLLBACK TRAN END
- GO
- IF @@TRANCOUNT = 0 BEGIN PRINT 'step 4 is completed with errors' SET NOEXEC ON END
- GO
- --step 5: create temp table tmp_TDU_UnidadeNegocio--------------------------------------------------
- CREATE TABLE [dbo].[tmp_TDU_UnidadeNegocio] (
- [CDU_Codigo] [nvarchar](10) COLLATE Latin1_General_CI_AS NOT NULL,
- [CDU_Descricao] [nvarchar](100) COLLATE Latin1_General_CI_AS NULL CONSTRAINT [TDU_UnidadeNegocio_CDU_Descricao_DF] DEFAULT ('Descrição'),
- [CDU_Prefixo] [nvarchar](2) COLLATE Latin1_General_CI_AS NOT NULL,
- [CDU_Observacoes] [nvarchar](512) COLLATE Latin1_General_CI_AS NULL,
- [CDU_PrefixoSugestao] [nvarchar](2) COLLATE Latin1_General_CI_AS NULL,
- [CDU_TamanhoCampoSugestao] [INT] NULL,
- [CDU_UsarSIGE] [bit] NULL,
- [CDU_PV] [bit] NULL,
- [CDU_CC] [bit] NULL,
- [DataInsercao] [datetime] NULL CONSTRAINT [DF_TDU_UnidadeNegocio_DataInsercao] DEFAULT (getdate()),
- [UtilizadorInsercao] [INT] NULL,
- [DataAtualizacao] [datetime] NULL CONSTRAINT [DF_TDU_UnidadeNegocio_DataAtualizacao] DEFAULT (getdate()),
- [UtilizadorAtualizacao] [INT] NULL
- ) ON [PRIMARY]
- GO
- IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 5 is completed with errors' ROLLBACK TRAN END
- GO
- IF @@TRANCOUNT = 0 BEGIN PRINT 'step 5 is completed with errors' SET NOEXEC ON END
- GO
- --step 6: copy existing data into new table tmp_TDU_UnidadeNegocio----------------------------------
- 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
- [CDU_Codigo],
- [CDU_Descricao],
- [CDU_Prefixo],
- [CDU_Observacoes],
- [CDU_PrefixoSugestao],
- [CDU_TamanhoCampoSugestao],
- [CDU_UsarSIGE],
- NULL,
- NULL,
- [DataInsercao],
- [UtilizadorInsercao],
- [DataAtualizacao],
- [UtilizadorAtualizacao]
- FROM [dbo].[TDU_UnidadeNegocio]
- GO
- IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 6 is completed with errors' ROLLBACK TRAN END
- GO
- IF @@TRANCOUNT = 0 BEGIN PRINT 'step 6 is completed with errors' SET NOEXEC ON END
- GO
- --step 7: dbo.Artigo: drop foreign key FK_Artigo_TDU_UnidadeNegocio---------------------------------
- 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]'))
- ALTER TABLE [dbo].[Artigo] DROP CONSTRAINT [FK_Artigo_TDU_UnidadeNegocio]
- GO
- IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 7 is completed with errors' ROLLBACK TRAN END
- GO
- IF @@TRANCOUNT = 0 BEGIN PRINT 'step 7 is completed with errors' SET NOEXEC ON END
- GO
- --step 8: dbo.COP_Obras: drop foreign key FK_COP_Obras_TDU_UnidadeNegocio---------------------------
- 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]'))
- ALTER TABLE [dbo].[COP_Obras] DROP CONSTRAINT [FK_COP_Obras_TDU_UnidadeNegocio]
- GO
- IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 8 is completed with errors' ROLLBACK TRAN END
- GO
- IF @@TRANCOUNT = 0 BEGIN PRINT 'step 8 is completed with errors' SET NOEXEC ON END
- GO
- --step 9: dbo.TDU_Obras: drop foreign key FK_TDU_Obras_TDU_UnidadeNegocio---------------------------
- 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]'))
- ALTER TABLE [dbo].[TDU_Obras] DROP CONSTRAINT [FK_TDU_Obras_TDU_UnidadeNegocio]
- GO
- IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 9 is completed with errors' ROLLBACK TRAN END
- GO
- IF @@TRANCOUNT = 0 BEGIN PRINT 'step 9 is completed with errors' SET NOEXEC ON END
- GO
- --step 10: drop table dbo.TDU_UnidadeNegocio--------------------------------------------------------
- IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TDU_UnidadeNegocio]') AND TYPE IN (N'U'))
- DROP TABLE [dbo].[TDU_UnidadeNegocio]
- GO
- IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 10 is completed with errors' ROLLBACK TRAN END
- GO
- IF @@TRANCOUNT = 0 BEGIN PRINT 'step 10 is completed with errors' SET NOEXEC ON END
- GO
- --step 11: rename tmp_TDU_UnidadeNegocio to dbo.TDU_UnidadeNegocio----------------------------------
- DECLARE @i INT
- EXEC @i = sp_rename N'[dbo].[tmp_TDU_UnidadeNegocio]', N'TDU_UnidadeNegocio'
- IF @i <> 0 BEGIN PRINT 'step 11 is completed with errors' ROLLBACK TRAN END
- GO
- IF @@TRANCOUNT = 0 BEGIN PRINT 'step 11 is completed with errors' SET NOEXEC ON END
- GO
- --step 12: dbo.TDU_UnidadeNegocio: add primary key PK_TDU_UnidadeNegocio----------------------------
- IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[TDU_UnidadeNegocio]') AND name = N'PK_TDU_UnidadeNegocio')
- ALTER TABLE [dbo].[TDU_UnidadeNegocio] ADD CONSTRAINT [PK_TDU_UnidadeNegocio] PRIMARY KEY CLUSTERED ([CDU_Codigo])
- GO
- IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 12 is completed with errors' ROLLBACK TRAN END
- GO
- IF @@TRANCOUNT = 0 BEGIN PRINT 'step 12 is completed with errors' SET NOEXEC ON END
- GO
- --step 13: dbo.Departamentos: add foreign key FK_Departamentos_TDU_UnidadeNegocio-------------------
- 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]'))
- ALTER TABLE [dbo].[Departamentos] ADD CONSTRAINT [FK_Departamentos_TDU_UnidadeNegocio] FOREIGN KEY ([CDU_UnidadeNegocio]) REFERENCES [dbo].[TDU_UnidadeNegocio] ([CDU_Codigo])
- GO
- IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 13 is completed with errors' ROLLBACK TRAN END
- GO
- IF @@TRANCOUNT = 0 BEGIN PRINT 'step 13 is completed with errors' SET NOEXEC ON END
- GO
- --step 14: add columns to Table dbo.Zonas-----------------------------------------------------------
- ALTER TABLE [dbo].[Zonas] ADD
- [CDU_PAV] [bit] NULL,
- [CDU_UnidadeNegocio] [nvarchar](10) COLLATE Latin1_General_CI_AS NULL
- GO
- IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 14 is completed with errors' ROLLBACK TRAN END
- GO
- IF @@TRANCOUNT = 0 BEGIN PRINT 'step 14 is completed with errors' SET NOEXEC ON END
- GO
- --step 15: dbo.Zonas: add foreign key FK_Zonas_TDU_UnidadeNegocio-----------------------------------
- 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]'))
- ALTER TABLE [dbo].[Zonas] ADD CONSTRAINT [FK_Zonas_TDU_UnidadeNegocio] FOREIGN KEY ([CDU_UnidadeNegocio]) REFERENCES [dbo].[TDU_UnidadeNegocio] ([CDU_Codigo])
- GO
- IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 15 is completed with errors' ROLLBACK TRAN END
- GO
- IF @@TRANCOUNT = 0 BEGIN PRINT 'step 15 is completed with errors' SET NOEXEC ON END
- GO
- --step 16: dbo.TDU_UnidadeNegocio: add foreign key FK_TDU_UnidadeNegocio_Users1---------------------
- 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]'))
- ALTER TABLE [dbo].[TDU_UnidadeNegocio] ADD CONSTRAINT [FK_TDU_UnidadeNegocio_Users1] FOREIGN KEY ([UtilizadorAtualizacao]) REFERENCES [dbo].[Users] ([UserId])
- GO
- IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 16 is completed with errors' ROLLBACK TRAN END
- GO
- IF @@TRANCOUNT = 0 BEGIN PRINT 'step 16 is completed with errors' SET NOEXEC ON END
- GO
- --step 17: dbo.TDU_UnidadeNegocio: add foreign key FK_TDU_UnidadeNegocio_Users----------------------
- 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]'))
- ALTER TABLE [dbo].[TDU_UnidadeNegocio] ADD CONSTRAINT [FK_TDU_UnidadeNegocio_Users] FOREIGN KEY ([UtilizadorInsercao]) REFERENCES [dbo].[Users] ([UserId])
- GO
- IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 17 is completed with errors' ROLLBACK TRAN END
- GO
- IF @@TRANCOUNT = 0 BEGIN PRINT 'step 17 is completed with errors' SET NOEXEC ON END
- GO
- --step 18: dbo.TDU_Obras: add foreign key FK_TDU_Obras_TDU_UnidadeNegocio---------------------------
- 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]'))
- ALTER TABLE [dbo].[TDU_Obras] ADD CONSTRAINT [FK_TDU_Obras_TDU_UnidadeNegocio] FOREIGN KEY ([CDU_UnidadeNegocio]) REFERENCES [dbo].[TDU_UnidadeNegocio] ([CDU_Codigo])
- GO
- IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 18 is completed with errors' ROLLBACK TRAN END
- GO
- IF @@TRANCOUNT = 0 BEGIN PRINT 'step 18 is completed with errors' SET NOEXEC ON END
- GO
- --step 19: dbo.COP_Obras: add foreign key FK_COP_Obras_TDU_UnidadeNegocio---------------------------
- 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]'))
- ALTER TABLE [dbo].[COP_Obras] ADD CONSTRAINT [FK_COP_Obras_TDU_UnidadeNegocio] FOREIGN KEY ([CDU_UnidadeNegocio]) REFERENCES [dbo].[TDU_UnidadeNegocio] ([CDU_Codigo])
- GO
- IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 19 is completed with errors' ROLLBACK TRAN END
- GO
- IF @@TRANCOUNT = 0 BEGIN PRINT 'step 19 is completed with errors' SET NOEXEC ON END
- GO
- --step 20: dbo.Artigo: add foreign key FK_Artigo_TDU_UnidadeNegocio---------------------------------
- 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]'))
- ALTER TABLE [dbo].[Artigo] ADD CONSTRAINT [FK_Artigo_TDU_UnidadeNegocio] FOREIGN KEY ([CDU_UnidadeNegocio]) REFERENCES [dbo].[TDU_UnidadeNegocio] ([CDU_Codigo])
- GO
- IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 20 is completed with errors' ROLLBACK TRAN END
- GO
- IF @@TRANCOUNT = 0 BEGIN PRINT 'step 20 is completed with errors' SET NOEXEC ON END
- GO
- ----------------------------------------------------------------------
- IF @@TRANCOUNT > 0 BEGIN COMMIT TRAN PRINT 'Synchronization is successfully completed.' END
- GO
- SET NOEXEC OFF
- GO
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement