Rename an AX company on SQL

This is something I will never recommend to anyone, but I had to do it for a client and desperate time called for desperate measure. I did this on AX 2012 R3 and it has worked well.

Make sure to change the name of from and to company in the following. 

Step 1)  (Taken and modified http://www.artofcreation.be/2010/02/24/rename-an-ax-company-on-sql/)

EXEC sp_MSforeachtable 'update ? set DataAreaID = "TOCOmpany" where ?.DataAreaID = "FROMCompany"'

UPDATE DataArea SET ID = 'TOCOmpany' WHERE DataArea.ID = 'FROMCompany'

Step 2) This is the script I manually created to overwrite the company name across all relevant instances including number sequences, Account structures and what not.

  declare @fromCompany VARCHAR(MAX);
  set @fromCompany = 'FROM COMPANY';
    declare @toCompany VARCHAR(MAX);
  set @toCompany = 'TO COMPANY';

EXEC sp_MSforeachtable 'update ? set DataAreaID = @toCompany where ?.DataAreaID = @fromCompany 

  update [AX_Dynamics_DMTesting].[dbo].[BANKACCOUNTTABLE]
set [dbo].[BANKACCOUNTTABLE].[COMPANYPAYMID] = @toCompany 
where [dbo].[BANKACCOUNTTABLE].[COMPANYPAYMID] = @fromCompany 

  update  [AX_Dynamics_DMTesting].[dbo].[ECORESSTORAGEDIMENSIONGROUPITEM]
set [dbo].[ECORESSTORAGEDIMENSIONGROUPITEM].ITEMDATAAREAID = @toCompany 
where [dbo].[ECORESSTORAGEDIMENSIONGROUPITEM].ITEMDATAAREAID = @fromCompany 

update [AX_Dynamics_DMTesting].[dbo].[USERINFO]
set [dbo].[USERINFO].[COMPANY] = @toCompany 
where [dbo].[USERINFO].[COMPANY] = @fromCompany 

update [AX_Dynamics_DMTesting].[dbo].[TAXUNCOMMITTED]
set [dbo].[TAXUNCOMMITTED].[COMPANY] = @toCompany 
where [dbo].[TAXUNCOMMITTED].[COMPANY] = @fromCompany 

  update  [AX_Dynamics_DMTesting].[dbo].BATCH
set [dbo].BATCH.COMPANY = @toCompany 
where [dbo].BATCH.COMPANY = @fromCompany 

  update  [AX_Dynamics_DMTesting].[dbo].DATAAREA
set [dbo].dataarea.ID = @toCompany 
where [dbo].DATAAREA.ID = @fromCompany 

  update  [AX_Dynamics_DMTesting].[dbo].[DIRPARTYRELATIONSHIP]
set [dbo].[DIRPARTYRELATIONSHIP].[LEGALENTITYDATAAREAID] = @toCompany 
where [dbo].[DIRPARTYRELATIONSHIP].[LEGALENTITYDATAAREAID] = @fromCompany 

  update  [AX_Dynamics_DMTesting].[dbo].[INVENTMODELGROUPITEM]
set [dbo].[INVENTMODELGROUPITEM].[MODELGROUPDATAAREAID] = @toCompany 
where [dbo].[INVENTMODELGROUPITEM].[MODELGROUPDATAAREAID] = @fromCompany 

  update  [AX_Dynamics_DMTesting].[dbo].[INVENTMODELGROUPITEM]
set [dbo].[INVENTMODELGROUPITEM].[ITEMDATAAREAID] = @toCompany 
where [dbo].[INVENTMODELGROUPITEM].[ITEMDATAAREAID] = @fromCompany 

  update  [AX_Dynamics_DMTesting].[dbo].[INVENTTRANSORIGINJOURNALTRANS]
set [dbo].[INVENTTRANSORIGINJOURNALTRANS].[INVENTJOURNALDATAAREAID] = @toCompany 
where [dbo].[INVENTTRANSORIGINJOURNALTRANS].[INVENTJOURNALDATAAREAID] = @fromCompany 

  update  [AX_Dynamics_DMTesting].[dbo].[LEDGERJOURNALTRANS]
set [dbo].[LEDGERJOURNALTRANS].[OFFSETCOMPANY] = @toCompany 
where [dbo].[LEDGERJOURNALTRANS].[OFFSETCOMPANY] = @fromCompany 

  update  [AX_Dynamics_DMTesting].[dbo].[EVENTINBOX]
set [dbo].[EVENTINBOX].[COMPANYID] = @toCompany 
where [dbo].[EVENTINBOX].[COMPANYID] = @fromCompany 

  update  [AX_Dynamics_DMTesting].[dbo].[PURCHREQLINEHISTORY]
set [dbo].[PURCHREQLINEHISTORY].[INVENTDIMIDDATAAREA] = @toCompany 
where [dbo].[PURCHREQLINEHISTORY].[INVENTDIMIDDATAAREA] = @fromCompany 

  update  [AX_Dynamics_DMTesting].[dbo].[PURCHREQLINEHISTORY]
set [dbo].[PURCHREQLINEHISTORY].ITEMIDDATAAREA = @toCompany 
where [dbo].[PURCHREQLINEHISTORY].ITEMIDDATAAREA = @fromCompany 

  update  [AX_Dynamics_DMTesting].[dbo].[PURCHREQLINEHISTORY]
set [dbo].[PURCHREQLINEHISTORY].[PROJIDDATAAREA] = @toCompany 
where [dbo].[PURCHREQLINEHISTORY].[PROJIDDATAAREA] = @fromCompany 

  update  [AX_Dynamics_DMTesting].[dbo].[PURCHREQLINEHISTORY]
set [dbo].[PURCHREQLINEHISTORY].[PROJTAXGROUPIDDATAAREA] = @toCompany 
where [dbo].[PURCHREQLINEHISTORY].[PROJTAXGROUPIDDATAAREA] = @fromCompany 

  update  [AX_Dynamics_DMTesting].[dbo].[PURCHREQLINEHISTORY]
set [dbo].[PURCHREQLINEHISTORY].[PROJTRANSIDDATAAREA] = @toCompany 
where [dbo].[PURCHREQLINEHISTORY].[PROJTRANSIDDATAAREA] = @fromCompany 

  update  [AX_Dynamics_DMTesting].[dbo].[PURCHREQLINEHISTORY]
set [dbo].[PURCHREQLINEHISTORY].[TAXITEMGROUPDATAAREA] = @toCompany 
where [dbo].[PURCHREQLINEHISTORY].[TAXITEMGROUPDATAAREA] = @fromCompany 

  update  [AX_Dynamics_DMTesting].[dbo].[PURCHREQLINEHISTORY]
set [dbo].[PURCHREQLINEHISTORY].[VENDACCOUNTDATAAREA] = @toCompany
where [dbo].[PURCHREQLINEHISTORY].[VENDACCOUNTDATAAREA] = @fromCompany


  update  [AX_Dynamics_DMTesting].[dbo].[PURCHREQTABLE]
set [dbo].[PURCHREQTABLE].[PROJIDDATAAREA] = @toCompany
where [dbo].[PURCHREQTABLE].[PROJIDDATAAREA] = @fromCompany

  update  [AX_Dynamics_DMTesting].[dbo].[PURCHREQTABLEHISTORY]
set [dbo].[PURCHREQTABLEHISTORY].[PROJIDDATAAREA] = @toCompany
where [dbo].[PURCHREQTABLEHISTORY].[PROJIDDATAAREA] = @fromCompany

 update  [AX_Dynamics_DMTesting].[dbo].[SUBLEDGERJOURNALENTRY]
set [dbo].[SUBLEDGERJOURNALENTRY].[VOUCHERDATAAREAID] = @toCompany
where [dbo].[SUBLEDGERJOURNALENTRY].[VOUCHERDATAAREAID] = @fromCompany

  update  [AX_Dynamics_DMTesting].[dbo].[SUBLEDGERVOUCHERGENERALJOURNALENTRY]
set [dbo].[SUBLEDGERVOUCHERGENERALJOURNALENTRY].[VOUCHERDATAAREAID] = @toCompany
where [dbo].[SUBLEDGERVOUCHERGENERALJOURNALENTRY].[VOUCHERDATAAREAID] = @fromCompany

  update  [AX_Dynamics_DMTesting].[dbo].[INVENTITEMGROUPITEM]
set [dbo].[INVENTITEMGROUPITEM].[ITEMGROUPDATAAREAID] = @toCompany 
where [dbo].[INVENTITEMGROUPITEM].[ITEMGROUPDATAAREAID] = @fromCompany 
  
  update  [AX_Dynamics_DMTesting].[dbo].[INVENTITEMGROUPITEM]
set [dbo].[INVENTITEMGROUPITEM].[ITEMDATAAREAID] = @toCompany 
where [dbo].[INVENTITEMGROUPITEM].[ITEMDATAAREAID] = @fromCompany 
  
  update  [AX_Dynamics_DMTesting].[dbo].[INVENTITEMSETUPSUPPLYTYPE]
set [dbo].[INVENTITEMSETUPSUPPLYTYPE].[ITEMDATAAREAID] = @toCompany 
where [dbo].[INVENTITEMSETUPSUPPLYTYPE].[ITEMDATAAREAID] = @fromCompany 

  update  [AX_Dynamics_DMTesting].[dbo].[LEDGERINTERCOMPANY]
set [dbo].[LEDGERINTERCOMPANY].[COMPANY] = @toCompany 
where [dbo].[LEDGERINTERCOMPANY].[COMPANY] = @fromCompany 
  
  update  [AX_Dynamics_DMTesting].[dbo].[LEDGER]
set [dbo].[LEDGER].[NAME] = @toCompany 
where [dbo].[LEDGER].[NAME] = @fromCompany 
  
  update  [AX_Dynamics_DMTesting].[dbo].[LEDGERJOURNALTRANS]
set [dbo].[LEDGERJOURNALTRANS].[COMPANY] = @toCompany 
where [dbo].[LEDGERJOURNALTRANS].[COMPANY] = @fromCompany 
  
  update  [AX_Dynamics_DMTesting].[dbo].[PURCHREQLINE]
set [dbo].[PURCHREQLINE].[INVENTDIMIDDATAAREA] = @toCompany 
where [dbo].[PURCHREQLINE].[INVENTDIMIDDATAAREA] = @fromCompany

  update  [AX_Dynamics_DMTesting].[dbo].[PURCHREQLINE]
set [dbo].[PURCHREQLINE].[ITEMIDDATAAREA] = @toCompany 
where [dbo].[PURCHREQLINE].[ITEMIDDATAAREA] = @fromCompany 
  
  update  [AX_Dynamics_DMTesting].[dbo].[PURCHREQLINE]
set [dbo].[PURCHREQLINE].[PROJTAXGROUPIDDATAAREA] = @toCompany 
where [dbo].[PURCHREQLINE].[PROJTAXGROUPIDDATAAREA] = @fromCompany
  
  update  [AX_Dynamics_DMTesting].[dbo].[PURCHREQLINE]
set [dbo].[PURCHREQLINE].[PROJTRANSIDDATAAREA] = @toCompany 
where [dbo].[PURCHREQLINE].[PROJTRANSIDDATAAREA] = @fromCompany
  
  update  [AX_Dynamics_DMTesting].[dbo].[PURCHREQLINE]
set [dbo].[PURCHREQLINE].[PURCHIDDATAAREA] = @toCompany 
where [dbo].[PURCHREQLINE].[PURCHIDDATAAREA] = @fromCompany
  
  update  [AX_Dynamics_DMTesting].[dbo].[PURCHREQLINE]
set [dbo].[PURCHREQLINE].[TAXGROUPDATAAREA] = @toCompany 
where [dbo].[PURCHREQLINE].[TAXGROUPDATAAREA] = @fromCompany
  
  update  [AX_Dynamics_DMTesting].[dbo].[PURCHREQLINE]
set [dbo].[PURCHREQLINE].[TAXITEMGROUPDATAAREA] = @toCompany 
where [dbo].[PURCHREQLINE].[TAXITEMGROUPDATAAREA] = @fromCompany
  
  update  [AX_Dynamics_DMTesting].[dbo].[PURCHREQLINE]
set [dbo].[PURCHREQLINE].[VENDACCOUNTDATAAREA] = @toCompany 
where [dbo].[PURCHREQLINE].[VENDACCOUNTDATAAREA] = @fromCompany
  
  update  [AX_Dynamics_DMTesting].[dbo].[PURCHTABLEVERSION]
set [dbo].[PURCHTABLEVERSION].[PURCHIDDATAAREAID] = @toCompany 
where [dbo].[PURCHTABLEVERSION].[PURCHIDDATAAREAID] = @fromCompany

  update  [AX_Dynamics_DMTesting].[dbo].[WORKCALENDAREMPLOYMENT]
set [dbo].[WORKCALENDAREMPLOYMENT].[CALENDARDATAAREAID] = @toCompany 
where [dbo].[WORKCALENDAREMPLOYMENT].[CALENDARDATAAREAID] = @fromCompany
  
  update  [AX_Dynamics_DMTesting].[dbo].[WORKFLOWTABLE]
set [dbo].[WORKFLOWTABLE].[DATAAREA] = @toCompany 
where [dbo].[WORKFLOWTABLE].[DATAAREA] = @fromCompany

update  [AX_Dynamics_DMTesting].[dbo].[ECORESTRACKINGDIMENSIONGROUPITEM]
set [dbo].[ECORESTRACKINGDIMENSIONGROUPITEM].[ITEMDATAAREAID] = @toCompany 
where [dbo].[ECORESTRACKINGDIMENSIONGROUPITEM].[ITEMDATAAREAID] = @fromCompany
  
  update  [AX_Dynamics_DMTesting].[dbo].[GENERALJOURNALENTRY]
set [dbo].[GENERALJOURNALENTRY].[SUBLEDGERVOUCHERDATAAREAID] = @toCompany 
where [dbo].[GENERALJOURNALENTRY].[SUBLEDGERVOUCHERDATAAREAID] = @fromCompany
  
  update  [AX_Dynamics_DMTesting].[dbo].[INVENTTRANSORIGINPURCHLINE]
set [dbo].[INVENTTRANSORIGINPURCHLINE].[PURCHLINEDATAAREAID] = @toCompany 
where [dbo].[INVENTTRANSORIGINPURCHLINE].[PURCHLINEDATAAREAID] = @fromCompany

  update  [AX_Dynamics_DMTesting].[dbo].[NUMBERSEQUENCESCOPE]
set [dbo].[NUMBERSEQUENCESCOPE].[DATAAREA] = @toCompany 
where [dbo].[NUMBERSEQUENCESCOPE].[DATAAREA] = @fromCompany

  update  [AX_Dynamics_DMTesting].[dbo].[DirPartyTable]
set [dbo].[DirPartyTable].[DATAAREA] = @toCompany 
where [dbo].[DirPartyTable].[DATAAREA] = @fromCompany

  UPDATE [AX_Dynamics_DMTesting].[dbo].[NUMBERSEQUENCEHISTORY]
SET [NUMBERSEQUENCEHISTORY].[FORMAT] = @toCompany + SUBSTRING([FORMAT], 5, Len([FORMAT]) - 4)
WHERE [FORMAT] LIKE @fromCompany + '%' 

  UPDATE
[AX_Dynamics_DMTesting].[dbo].[NUMBERSEQUENCETABLE]
SET
[NUMBERSEQUENCETABLE].[FORMAT] = @toCompany + SUBSTRING([FORMAT], 5, Len([FORMAT]) - 4)
WHERE
[FORMAT] LIKE @fromCompany + '%' 
  

Step 3)  Search for all remaining instances by adding the fromCompany name to this query
Step 4) For some reason, the DirPartyTable creates a new record for a new company instead of updating it. You might need to Select to 1000 Rows in there and see if new company is created. There will be two rows where the DATAAREA column will have the new company name and the other one with the old company name. Manually delete the new company name and update the old one using the following command. ONLY RUN THIS IF THERE IS AN EXTRA RECORD WITH NEW COMPANY!

delete
from [AX_Dynamics_DMTesting].[dbo].[DIRPARTYTABLE]
  where [dbo].[DIRPARTYTABLE].DATAAREA = 'new company'

update [AX_Dynamics_DMTesting].[dbo].[DIRPARTYTABLE]
set [dbo].[DIRPARTYTABLE].DATAAREA = 'NewCompany'
where [dbo].[DIRPARTYTABLE].DATAAREA = 'OLDCompany'