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: 
In order to handle MAX and exclude the niggly sysdiagrams:

```
SELECT 
'
ALTER TABLE [' + TABLE_SCHEMA + '].[' + TABLE_NAME + '] 
ALTER COLUMN [' + COLUMN_NAME + '] 
VARCHAR(' + 
    (CASE WHEN CHARACTER_MAXIMUM_LENGTH = -1 
        THEN 'MAX' 
        ELSE CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR) 
    END)
 + ')
'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE = 'NVARCHAR' AND TABLE_NAME <> 'SYSDIAGRAMS'

```
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.)