How to change every nvarchar column to varchar?

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 1

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

Answer 2

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.

Answer 3

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'