Changing all VARCHAR to NVARCHAR database-wide?

Hi,
I have an ASP.NET application that uses VARCHAR extensively in the tables and, more importantly, stored procedures (a couple hundred of them).

This app needs to start accepting foreign language in some areas, so I was wondering if there was some way to go through the tables and, more importantly, the stored procedures and change all "VARCHAR" references to "NVARCHAR" ?

Are the stored procedures stored as a text file somewhere on the server? If so I could use some sort of "replace" software utility to go through and change all VARCHAR to NVARCHAR

thanks!

-BretYou should be able to write a script to change your tables - it will take you a while since you probably will need to:

For each table with Varchar columns
- Create a temp table
- Copy all the data to the temp table
- Remove all FK constraints to the table
- Drop the table
- Create the table with NVarchar columns
- Insert the data from the temp table
- Create the IDENTITY column if applicable
- Recreate all the FK constraints

If you have fewer than 100 tables or so it might be quicker to use EM to do this.

For the stored procs: The quickest way to do the change is to allow updates on system tables. You can change this in EM. Then you can find all the stored procedures in sysobjects, with the actual TSQL in syscomments (in the 'text' column) Of course it's not normally a good idea to update system tables directly, and I'm not 100% sure this will work, but it's worth a shot:

update C
 set C.Text = replace(C.[Text], 'varchar', 'nvarchar')
 from syscomments C
 join sysobjects O
 on C.[id] = O.[id]
 and O.Type = 'P'

Now obviously there's a lot of things that can go wrong here. You string 'varchar' may exist as a part of a variable name, or column name or some other place where it's not supposed to be replaced by 'nvarchar'. It's possible that a stored procedure is long enough to use more than one row in syscomments, and this will be a problem since 'nvarchar' is one character more than 'varchar' so the update will fail with 'string or binary data would be truncated'.

So be careful and be sure to test every SP. Again you can write a script to execute every stored proc in your DB and check for errors.

Have fun!|||You have to change to Unicode Collation, nothing serious in SQL Server 2000 you can change it down to column level but Unicode in SQL Server is Multi byte unlike .NET where you can choose UTF 8 which is equivalent to ASCII. Yes Stored Procs are stored in .SQL text files that you can open with notepad and can be seen under stored procs in the Enterprise Manager in your Database. Run a search in the BOL(books online) for Mixed collation and ALTER PROCEDURE. If you are now using UTF 8 in .NET you may not run into storage problems except disk space which will double. Stored procs in SQL Server 2000 can be up to 128 megs in size. Hope this helps.

Kind regards,
Gift Peddie