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.
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'

```