DECLARE
@StagingTable NVARCHAR(100) = 'StagingTableOldName'
DECLARE
@NewStagingTable NVARCHAR(100) = 'StagingTableNewName'
DECLARE @EntityID
INT
DECLARE db_cursor
CURSOR FOR
SELECT e.ID,
e.StagingBase, @NewStagingTable
FROM [mdm].[tblEntity] e
INNER JOIN mdm.tblModel m ON e.Model_ID =
m.ID
WHERE m.Name = 'MTN'
AND e.StagingBase = @StagingTable
OPEN db_cursor
FETCH NEXT FROM
db_cursor INTO @EntityID, @StagingTable, @NewStagingTable
WHILE @@FETCH_STATUS
= 0
BEGIN
PRINT '---- ' +
@StagingTable
PRINT
'UPDATE mdm.tblEntity SET StagingBase = ' + QUOTENAME(@NewStagingTable, '''') +
' WHERE ID = ' + CAST(@EntityID AS NVARCHAR(10))
PRINT
'DROP PROC stg.udp_' + @StagingTable + '_Leaf'
PRINT
'DROP VIEW stg.viw_' + @StagingTable + '_MemberErrorDetails'
PRINT
'EXEC sp_rename ' + QUOTENAME('stg.' + @StagingTable + '_Leaf', '''') + ', ' +
QUOTENAME(@NewStagingTable + '_Leaf', '''')
PRINT
'EXEC sp_rename ' + QUOTENAME('stg.' + @NewStagingTable + '_Leaf.pk_' +
@StagingTable+ '_Leaf', '''') + ', ' + QUOTENAME('pk_' + @NewStagingTable +
'_Leaf', '''')
PRINT
'EXEC sp_rename ' + QUOTENAME('stg.ck_' + @StagingTable + '_Leaf_ImportType',
'''') + ', ' + QUOTENAME('ck_' + @NewStagingTable + '_Leaf_ImportType', '''')
PRINT
'EXEC sp_rename ' + QUOTENAME('stg.ck_' + @StagingTable +
'_Leaf_ImportStatus_ID', '''') + ', ' + QUOTENAME('ck_' + @NewStagingTable +
'_Leaf_ImportStatus_ID', '''')
PRINT
'EXEC sp_rename ' + QUOTENAME('stg.' + @NewStagingTable + '_Leaf.ix_' +
@StagingTable + '_Leaf_Batch_ID', '''') + ', ' + QUOTENAME('ix_' +
@NewStagingTable + '_Leaf_Batch_ID', '''')
PRINT
'EXEC sp_rename ' + QUOTENAME('stg.' + @NewStagingTable + '_Leaf.ix_' +
@StagingTable + '_Leaf_BatchTag', '''') + ', ' + QUOTENAME('ix_' +
@NewStagingTable + '_Leaf_BatchTag', '''')
PRINT
'EXEC sp_rename ' + QUOTENAME('stg.df_' + @StagingTable +
'_Leaf_ImportStatus_ID', '''') + ', ' + QUOTENAME('df_' + @NewStagingTable +
'_Leaf_ImportStatus_ID', '''')
PRINT
'EXEC mdm.udpEntityStagingCreateLeafStoredProcedure ' + CAST(@EntityID AS
NVARCHAR(10))
PRINT
'EXEC mdm.udpCreateEntityStagingErrorDetailViews ' + CAST(@EntityID AS
NVARCHAR(10))
PRINT
''
FETCH
NEXT FROM db_cursor INTO @EntityID, @StagingTable, @NewStagingTable
END
CLOSE db_cursor
DEALLOCATE db_cursor
