Update Company ID field across all company database

A couple of weeks back I needed to consolidate two DYNAMICS databases and move companies from the first DYNAMICS to the second, this for sure requires Company IDs to be changed as it might be taken by another companies on the main DYNAMICS, to fix this once for all I have wrote the following script:

DECLARE @Statement VARCHAR(8000)
DECLARE @TABLENAME VARCHAR(500)
DECLARE CURR Cursor FOR 
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'CMPANYID' AND COLUMN_DEFAULT IS NOT NULL
OPEN CURR
FETCH NEXT FROM CURR INTO @TABLENAME
WHILE @@FETCH_STATUS = 0 
BEGIN 
SET @Statement = 'SELECT * INTO ' + @TABLENAME + '_BAK FROM ' + @TABLENAME 
EXEC (@Statement)
SET @Statement = 'UPDATE ' + @TABLENAME + ' SET CMPANYID = [NEW COMPANY ID] WHERE CMPANYID = [OLD COMPANY ID]'
EXEC (@Statement)
FETCH NEXT FROM CURR INTO @TABLENAME
END
CLOSE CURR
DEALLOCATE CURR

 

Hope that this helps.

---

Regards,

--
Mohammad R. Daoud MVP - MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85
me@mohdaoud.com
http://www.di.jo