Thursday, October 11, 2012

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

3 comments:

  1. Thanks for sharing! Have you tested this against entities with Explicit Hierarchies enabled? Apparently it does not cover the "_Consolidated" and "_Relationship" tables.

    ReplyDelete
  2. thanks great script,
    i had to do some slight modification to the script to get it working as the entity name has a space in it.
    So if you have spaces in your entity name, the primary key, indexes and constraint names have spaces and not underscores like everywhere else

    ReplyDelete