sql server 2005 - 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

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.