Update Currency Index field across all database

You might need to change the currency index for one of your currencies, I needed this during a consolidation project for multiple DYNAMICS databases and needed to change the currency index field for all company tables, take a look into the below script, I have used the “Information_Schema” to get all columns that are called “CURRNIDX” and updated these using SQL Cursor, considering that I taking a backup for each table before doing the operation:

DECLARE @Statement VARCHAR(8000)
DECLARE @TABLENAME VARCHAR(500)
DECLARE CURR Cursor FOR 
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'CURRNIDX' 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 CURRNIDX = [NEW CURRENCY ID] WHERE CURRNIDX = [OLD CURRENCY ID]'
EXEC (@Statement)

FETCH NEXT FROM CURR INTO @TABLENAME
END
CLOSE CURR
DEALLOCATE CURR

---

Regards,

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