MDS Master Data Services 2012 Rename Entity Staging Table

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