USE TEMPORARY
go
SET NOCOUNT ON
GO
CREATE SCHEMA tTransOld
GO
CREATE SCHEMA tTransNew
GO
-- tTransOld tables
SELECT 'IF OBJECT_ID(''Temporary.tTransOld.' + TableName + ''') IS NOT NULL DROP TABLE Temporary.tTransOld.' + TableName + '
GO
SELECT * INTO Temporary.tTransOld.' + TableName + ' FROM Diablo.' + fqn + ' (NOLOCK) WHERE DataSupplierid = 544
GO
'
FROM Diablo.Metadata.Tables
WHERE SchemaName = 'tTrans'
GO
-- tTransNew Tables
;
WITH q AS (SELECT RingTable,
MAX(CASE WHEN ROW = 1 THEN ColName
END) AS Col1Name,
MAX(CASE WHEN ROW = 2 THEN ColName
END) AS Col2Name,
MAX(CASE WHEN ROW = 1 THEN prf
END) AS Prf1,
MAX(CASE WHEN ROW = 2 THEN prf
END) AS Prf2,
Util.dbo.StringConcat(FJoin, '') AS FJoin
FROM (SELECT RingTable,
b.ColName,
prf,
ROW_NUMBER() OVER (PARTITION BY RingTable ORDER BY b.ColName) AS ROW,
'
LEFT OUTER JOIN Diablo.tTrans.Trans f' + CAST(ROW_NUMBER() OVER (PARTITION BY RingTable ORDER BY b.ColName) AS VARCHAR) + ' (NOLOCK) ON f'
+ CAST(ROW_NUMBER() OVER (PARTITION BY RingTable ORDER BY b.ColName) AS VARCHAR) + '.DataSupplierId = a.DataSupplierID AND f'
+ CAST(ROW_NUMBER() OVER (PARTITION BY RingTable ORDER BY b.ColName) AS VARCHAR) + '.TransId = a.' + ColName AS FJoin
FROM Diablo.Metadata.ForeignKeys
CROSS APPLY (SELECT REPLACE (REPLACE (RingColumns, '[DataSupplierId], [', ''), ']', '') AS ColName) b
CROSS APPLY (SELECT LEFT (ColName, LEN (colname)- 7) AS Prf) p
WHERE RedColumns LIKE '%transid%'
AND CHARINDEX('[TransId]', RingColumns) = 0
AND RingSchema = 'ttrans') K
GROUP BY RingTable)
SELECT 'IF OBJECT_ID(''Temporary.tTransNew.' + t.TableName + ''') IS NOT NULL DROP TABLE Temporary.tTransNew.' + t.TableName + '
GO
SELECT
' + e.ColumnList + '
INTO Temporary.tTransNew.' + t.TableName + '
FROM Diablo.' + fqn + ' a (NOLOCK)
INNER JOIN Diablo.tTrans.Trans t (NOLOCK) ON t.DataSupplierid = a.DataSupplierid AND t.TransId = a.TransId' + ISNULL(FJoin, '') + '
WHERE a.DataSupplierid = 544
GO
'
FROM Diablo.Metadata.Tables t
CROSS APPLY (SELECT Util.dbo.StringConcat(CASE WHEN c.ColumnName = 'TransId' THEN 't.BatchDt, t.BatchSeq, t.MtgSeq'
ELSE 'a.' + c.ColumnName
END, ', ') AS ColumnList
FROM (SELECT TOP 999999
*
FROM Diablo.Metadata.Columns c
WHERE c.SchemaName = 'tTrans'
AND c.ObjectName = t.TableName
ORDER BY c.ColumnId) c) c
LEFT OUTER JOIN q ON q.RingTable = t.TableName
CROSS APPLY (SELECT CASE WHEN Prf1 IS NULL THEN c.ColumnList
ELSE REPLACE(c.ColumnList, 'a.' + Col1Name,
'f1.BatchDt as ' + Prf1 + 'BatchDt, f1.BatchSeq as ' + Prf1 + 'BatchSeq, f1.MtgSeq as ' + Prf1 + 'MtgSeq')
END AS ColumnList) d
CROSS APPLY (SELECT CASE WHEN Prf2 IS NULL THEN d.ColumnList
ELSE REPLACE(d.ColumnList, 'a.' + Col2Name,
'f2.BatchDt as ' + Prf2 + 'BatchDt, f2.BatchSeq as ' + Prf2 + 'BatchSeq, f2.MtgSeq as ' + Prf2 + 'MtgSeq')
END AS ColumnList) e
WHERE t.SchemaName = 'tTrans'
AND t.TableName <> 'Trans'
GO
-- TransOld indexes
DECLARE @SQL VARCHAR(MAX)
SELECT @SQL = 'USE Temporary
' + Util.dbo.StringConcat(REPLACE(REPLACE(REPLACE(REPLACE(CreateScript, '[tTrans]', '[tTransOld]'), 'DATA_COMPRESSION = PAGE', 'DATA_COMPRESSION = NONE'),
'DATA_COMPRESSION = PAGE', 'DATA_COMPRESSION = NONE'), 'ON [ps_', '--ON [ps_'), '
')
FROM Diablo.Metadata.TableScripts
WHERE SchemaName = 'tTrans'
AND type IN ('uQ', 'i', 'pk')
EXEC Util.dbo.PrintLargeText
@SQL
GO
-- TransNew indexes
DECLARE @SQL VARCHAR(MAX)
SELECT @SQL = 'USE Temporary
' + Util.dbo.StringConcat(REPLACE(REPLACE(REPLACE(REPLACE(NCS, '[tTrans]', '[tTransNew]'), 'DATA_COMPRESSION = PAGE', 'DATA_COMPRESSION = NONE'),
'DATA_COMPRESSION = PAGE', 'DATA_COMPRESSION = NONE'), 'ON [ps_', '--ON [ps_'), '
GO
')
FROM Diablo.Metadata.TableScripts
CROSS APPLY (SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CreateScript,
'WHERE ([FrclReleaseTransId] IS NOT NULL)', ''),
'WHERE ([LienTransId] IS NOT NULL)', ''),
'WHERE ([InvoluntaryLienReleaseTransId] IS NOT NULL)', ''),
'WHERE ([ForeClosedLienTransId] IS NOT NULL)', ''),
'WHERE ([DeedTransId] IS NOT NULL)', ''), '[FrclReleaseTransId]',
'FrclReleaseBatchDt, FrclReleaseBatchSeq, FrclReleaseMtgSeq'), '[LienTransId]',
'LienBatchDt, LienBatchSeq, LienMtgSeq'), '[InvoluntaryLienReleaseTransId]',
'InvoluntaryLienReleaseBatchDt, InvoluntaryLienReleaseBatchSeq, InvoluntaryLienReleaseMtgSeq'),
'[ForeClosedLienTransId]', 'ForeClosedLienBatchDt, ForeClosedLienBatchSeq, ForeClosedLienMtgSeq'), '[DeedTransId]',
'DeedBatchDt, DeedBatchSeq, DeedMtgSeq'), '[TransId]', 'BatchDt, BatchSeq, MtgSeq') AS NCS) b
WHERE SchemaName = 'tTrans'
AND type IN ('uQ', 'i', 'pk')
AND TableName <> 'Trans'
EXEC Util.dbo.PrintLargeText
@SQL
GO
RETURN
SELECT DISTINCT
REPLACE(REPLACE(LTRIM(RTRIM(c.field)), ']', ''), '[', '') AS Field
INTO #temp
FROM Diablo.Metadata.indexes i (NOLOCK)
CROSS APPLY Util.dbo.ParseDelimited(keycolumns + ISNULL(', ' + includecolumns, ''), ',') c
WHERE SchemaName = 'tTrans'
SELECT 'REPLACE(CreateScript, ''' + FIELD + ''', ''' + pf + 'BatchDt, ' + pf + 'BatchSeq, ' + pf + 'MtgSeq''),'
FROM #temp t
CROSS APPLY (SELECT LEFT (t.field, LEN (t.field)- 7) AS pf) p
WHERE field LIKE '%transid%'
AND field <> 'transid'
go
--TransOld FOREIGN Keys
SELECT REPLACE(CreateScript, '[tTrans]', '[tTransOld]')
FROM Diablo.Metadata.ForeignKeys
WHERE RedSchema = 'ttrans'
AND RingSchema = 'ttrans'
GO
--TransNew FOREIGN Keys
SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CreateScript, '[TransId]', 'BatchDt, BatchSeq, MtgSeq'), '[FrclReleaseTransId]',
'FrclReleaseBatchDt, FrclReleaseBatchSeq, FrclReleaseMtgSeq'), '[LienTransId]',
'LienBatchDt, LienBatchSeq, LienMtgSeq'), '[InvoluntaryLienReleaseTransId]',
'InvoluntaryLienReleaseBatchDt, InvoluntaryLienReleaseBatchSeq, InvoluntaryLienReleaseMtgSeq'),
'[ForeClosedLienTransId]', 'ForeClosedLienBatchDt, ForeClosedLienBatchSeq, ForeClosedLienMtgSeq'), '[DeedTransId]',
'DeedBatchDt, DeedBatchSeq, DeedMtgSeq'), '[tTrans]', '[tTransNew]')
FROM Diablo.Metadata.ForeignKeys
WHERE RedSchema = 'ttrans'
AND RingSchema = 'ttrans'
AND RedTable <> 'Trans'
GO