Hi,
First, I would suggest you leave the functional currencies as they are, and set up the new currencies as needed using the ISO standards. That's my disclaimer.
Second, you probably don't like first because the data is not consistent, so it can be done. The problem is you have both a currency index (CURRNIDX) and a currency id (CURNCYID) for transactions, functional id and index for each company (FUNLCURR) and (FUNCRIDX). You also have reporting currencies (RPTGCURR) (RPTCRIDX).
You will need to change the fields for both the id and index which is based off the master table in the DYNAMICS database for every table in the system that holds this information. There are a lot of tables. Depending on how many transactions are already in the database, and what modules are being used, this will be at least about a 150 tables per company database.
The general script used in sql would be:
USE <Company Database>
UPDATE <Table>
SET <Field> = <New Value>
WHERE <Field> = <Old Value>
This script will give you a list of all tables which hold a specific column name (this is by far more friendly than the script you found):
SELECT distinct(o.name) FROM sysobjects o, syscolumns c
WHERE o.id = c.id AND o.type = 'U'
AND c.name = 'CURNCYID' /* run for each field name */
ORDER BY o.name
You would have to run this script on every table that holds the currency id column and currency index number in the dynamics database and each company database. I would recommend you consult a professional that has performed this type of conversion to do this for you, and make sure you back up your databases in case something fails.
If you are not scared to death by this reply, you should be. Nearly all consultants/partners will tell you to create a new company database, export all of your records, then re-import to the new company. Changing the functional currency is generally not a good idea via SQL.
Regards,
Joshua Pelkola
