How to change each nvarchar column to varchar?

Question

What would be the easiest way to change each nvarchar column in a database to varchar?

I personally would prefer nvarchar, but the data arch indicated that varchar should 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 create all the necessary alter statements for you (cut, paste, run).

Please note that this does not require any restrictions.

Answer 2

To handle MAX and eliminate fuzzy 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' 

Answer 3

Ask the data arch to do this?

or

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

or

Write modified scripts to update an existing database.
(This may be the best approach if it is a production database or a client database.)