137 lines
5.2 KiB
Transact-SQL
137 lines
5.2 KiB
Transact-SQL
-- A cél adatbázison kell futtatni.
|
|
-- Üres DB-be másolásnál a @pChangeInTargetDB-nek 1-nek kell lenni, egyébként meg 0-nak
|
|
|
|
DROP PROCEDURE IF EXISTS dev.uspCloneIntezmenyList
|
|
GO
|
|
|
|
CREATE PROCEDURE dev.uspCloneIntezmenyList
|
|
@pSourceIntezmenyId nvarchar(255)
|
|
,@pSourceDatabase nvarchar(60)
|
|
,@pEntityHistoryMigration int /* 0 - Nem kell, 1 - csak migráció, 2 - átID-zás is */
|
|
,@pChangeIds bit /*1 - Target DB-ben, 0 - SourceDB-ben (az eredetileg üresben) */
|
|
,@pDebugMode bit = 0
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
DECLARE
|
|
@sql nvarchar(max)
|
|
,@sp nvarchar(max)
|
|
,@message nvarchar(max)
|
|
|
|
DROP TABLE IF EXISTS MappingTable
|
|
|
|
CREATE TABLE MappingTable (
|
|
tableName nvarchar(50),
|
|
oldId int,
|
|
newId int
|
|
)
|
|
|
|
SET @message = 'CLONE START - ' + FORMAT(GETDATE(), 'HH:mm:ss.ff')
|
|
RAISERROR (@message, 10, 1) WITH NOWAIT
|
|
-- ================================================================== --
|
|
-- TARGET - TRIGGER, CONSTRAINT kikapcsolás --
|
|
-- =======================================================holn======= --
|
|
SET @sql = N''
|
|
|
|
SELECT @sql += N'
|
|
ALTER INDEX ' + i.name + ' ON ' + t.name + N' DISABLE;'
|
|
FROM sys.indexes i
|
|
INNER JOIN sys.tables t ON t.object_id = i.object_id
|
|
WHERE i.type = 2 /*non clustered*/
|
|
AND t.name LIKE 'T[_]%' AND t.name <> 'T_ENTITYHISTORY'
|
|
AND t.schema_id = 1
|
|
|
|
SET @message = 'INDEX DISABLE... - ' + FORMAT(GETDATE(), 'HH:mm:ss.ff')
|
|
RAISERROR (@message, 10, 1) WITH NOWAIT
|
|
EXEC sp_executesql @sql
|
|
SET @message = 'INDEX DISABLE DONE - ' + FORMAT(GETDATE(), 'HH:mm:ss.ff')
|
|
RAISERROR ('INDEX DISABLE DONE', 10, 1) WITH NOWAIT
|
|
|
|
SET @sql = N''
|
|
|
|
SELECT @sql = @sql + N'
|
|
ALTER TABLE ' + OBJECT_NAME(fk.parent_object_id) + ' NOCHECK CONSTRAINT ' + OBJECT_NAME(fk.object_id) + CHAR(13) + CHAR(10)
|
|
FROM sys.foreign_keys fk
|
|
WHERE OBJECT_NAME(fk.parent_object_id) LIKE 'T[_]%'
|
|
|
|
SET @message = 'CONSTRAINT OFF... - ' + FORMAT(GETDATE(), 'HH:mm:ss.ff')
|
|
RAISERROR (@message, 10, 1) WITH NOWAIT
|
|
EXEC sp_executesql @sql
|
|
SET @message = 'CONSTRAINT OFF DONE - ' + FORMAT(GETDATE(), 'HH:mm:ss.ff')
|
|
RAISERROR (@message, 10, 1) WITH NOWAIT
|
|
|
|
|
|
SET @message = 'TRIGGER DISABLE... - ' + FORMAT(GETDATE(), 'HH:mm:ss.ff')
|
|
RAISERROR (@message, 10, 1) WITH NOWAIT
|
|
EXEC sp_msforeachtable 'DISABLE TRIGGER ALL ON ?'
|
|
SET @message = 'TRIGGER DISABLE DONE - ' + FORMAT(GETDATE(), 'HH:mm:ss.ff')
|
|
RAISERROR (@message, 10, 1) WITH NOWAIT
|
|
|
|
-- ============================================================== --
|
|
-- INTÉZMÉNY MÁSOLÁSA --
|
|
-- ============================================================== --
|
|
EXEC dev.uspCloneIntezmenyMigrationList
|
|
@pSourceIntezmenyId = @pSourceIntezmenyId
|
|
,@pSourceDatabase = @pSourceDatabase
|
|
,@pEntityHistoryMigration = @pEntityHistoryMigration
|
|
,@pChangeIds = @pChangeIds
|
|
,@pDebugMode = @pDebugMode
|
|
|
|
|
|
-- ============================================================== --
|
|
-- CHANGE IN SOURCE DB - @pChangeInTargetDB = 1 --
|
|
-- ============================================================== --
|
|
IF @pChangeIds = 1 BEGIN
|
|
|
|
-- ================================================================================ --
|
|
-- A MappingTable mentése a dev schemába --
|
|
-- ================================================================================ --
|
|
DECLARE @newIntezmenyId int
|
|
SET @sql = ''
|
|
|
|
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = N'dbo' AND TABLE_NAME = N'MappingTable') BEGIN
|
|
SELECT @newIntezmenyId = t1.[newId]
|
|
FROM MappingTable t1
|
|
WHERE t1.tableName = 'T_INTEZMENY'
|
|
|
|
SET @sql = 'SELECT * INTO dev.[MappingTable_' + CAST(@newIntezmenyId AS nvarchar(100)) + '_' + FORMAT(GETDATE(), 'yyyyMMdd_HHmm') + '_' + @pSourceDatabase + '] FROM MappingTable'
|
|
EXEC sp_executesql @sql
|
|
END
|
|
|
|
|
|
-- ============================================================== --
|
|
-- TARGET - TRIGGER, CONSTRAINT visszakapcsolás --
|
|
-- ============================================================== --
|
|
SET @sql = ''
|
|
SELECT @sql += 'ALTER INDEX ' + i.[name] + ' ON ' + t.[name] + ' REBUILD;' + char(13) + char(10)
|
|
FROM sys.indexes i
|
|
INNER JOIN sys.tables t ON t.object_id = i.object_id
|
|
WHERE i.type = 2 /*non clustered*/
|
|
AND t.name LIKE 'T[_]%'
|
|
AND t.schema_id = 1
|
|
|
|
|
|
RAISERROR (@sql, 10, 1) WITH NOWAIT
|
|
EXEC sp_executesql @sql
|
|
|
|
SET @sql = ''
|
|
SELECT @sql += N'ALTER TABLE ' + TABLE_NAME + N' WITH CHECK CHECK CONSTRAINT ALL; PRINT FORMAT(GETDATE(), ''yyyy-MM-dd HH:mm:ss.ff'') + '' - '' + ''' + TABLE_NAME + N''';' + CHAR(13) + CHAR(10)
|
|
FROM INFORMATION_SCHEMA.TABLES
|
|
WHERE TABLE_SCHEMA = N'dbo'
|
|
AND TABLE_NAME LIKE 'T[_]%'
|
|
AND TABLE_TYPE = N'BASE TABLE'
|
|
AND TABLE_NAME NOT IN(N'T_ENTITYHISTORY', N'T_ENTITYATTRIBUTEHISTORY', N'T_FELHASZNALOBELEPES')
|
|
--A check constreintek miatt a
|
|
SET @sql += N'
|
|
|
|
'
|
|
EXEC sp_executesql @sql
|
|
|
|
EXEC sp_msforeachtable 'ENABLE TRIGGER ALL ON ?'
|
|
|
|
|
|
END
|
|
END
|
|
GO
|
|
|