Question: what would be the simplest way to change every nvarchar column in a database to a varchar?

I personally would prefer nvarchar, but the data arch has specified that varchar must be used.

Answer: 
Here, to get you started:

```
Select 'Alter Table [' + TABLE_SCHEMA + '].[' + TABLE_NAME + '] Alter Column [' + COLUMN_NAME + '] VarChar(' + CAST(CHARACTER_MAXIMUM_LENGTH As VARCHAR) + ')'
From INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE = 'NVARCHAR'

```

This will generate all the needed alter statements for you (cut, paste, run).

Note that this does not take any constraints into account.
Ask the data arch to do it?

or

Generate a script of all objects in your system, alter then nvarchar's, then create a new database and import the data into it from the old one.

or

Write alter scripts to update the existing database.  

(This may be the best approach if it's a production database, or a client database.)