Make string columns unicode compliant

This script is designed allow all TEXT, NTEXT, CHAR and VARCHAR columns within a database to be changed en-masse to NVARCHAR. To accomplish this in the simplest way, we must drop all foreign keys, primary keys, indexes, default constraints, unique constraints and check constraints before altering the columns and then recreating all the dropped objects.

The purpose of this script is to generate, based on the system views of a database, all the nescesary DROP, ALTER and CREATE statements for this operation. All keys, indexes etc will be re-created exactly as they were in terms of column order, data sorting direction and so on, and foreign keys will also be re-created WITH NOCHECK if they were previously disabled (as will check constraints, althoguh if check constraints were previously enabled and trusted then a statement will be generated to ensure the constraint is set that way again). Schema names are automatically prepended to appropriate obejct names according to ownership. Indexes, Primary Keys and Unique Constraints will be assigned to the
appropriate filegroups automatically.

Apologies if any of the instructions seem a bit noddy - they were originally intended for colleagues who might not be the most fluent SQL developers and I might not have edited them enough!

LIMITATIONS

1. This script will only work on SQL Server 2005 and above as it uses system catalog views in the sys schema, and not INFORMATION_SCHEMA views or the old SQL 2000-style system tables.
2. Foreign keys that are disabled are re-created as disabled. However, if after this you simply re-enable the key this does not mean the optimiser will use it as the is_not_trusted field may not have been reset. To counter this, either drop the key re-create it WITH CHECK or user ALTER TABLE <tableName> CHECK CONSTRAINT ALL command. Avoid using the DBCC CHECKCONSTRAINTS command as this has been deprecated after SQL 2005.
3. This script was designed to work with a specific database and thus only encompasses those objects which would be a barrier to changing the string type columns in that database. In theory at least, this script should work on ANY database, however it may not take into account all objects which could potentially affect a column, and therefore it may require additional script blocks for those objects. It will NOT work on SQL 2000.
4. Objects covered: Primary Keys, Foreign Keys, Indexes, Default Constraints, Unique Constraints, Check Constraints.
5. Does NOT currently script Heap, XML or Spatial indexes.

USAGE

1. Ensure that all users are disconnected from the database. However, do not put the database into single user mode as if something goes wrong it can be difficult to get it out again, and if you back up a database in single user mode, it will restore in single user mode.
2. As always, before making any structural or data changes to any database YOU MSUT ENSURE THAT YOU BACK UP THE DATABASE FIRST AND THEN VERIFY THAT YOU CAN SUCCESSFUULY RESTORE THAT BACKUP. Do not assume that just because you have performed a backup that you are protected, always verify that the backup will restore correctly before you proceed. Once you have a functioning backup, it is highly recommended that you make a dry-run of this procedure by trying it on a restored copy of the database before running it against the final target, especially if the final target is a production platform.
3. Ensure that you replace <DBName> in the USE statement near the top with the name of the database that you wish to alter.
4. Ensure that you output results to text and set the column width to 8192 (In SSMS go to Tools -> Options and select the Query Results node in the treeview on the left. Change the 'Default destination for results' drop down to 'Results to text'. Now expand the 'Query Results' node and then the 'SQL Server' node below it, and finally select the 'Results To Text' node. Set the 'Maximum number of characters displayed in each column' field to 8192 (this is the largest allowed value). Now click OK. These changes will only be applied to new query windows so if needs be, open a new query window and then re-open this script.
5. Run this script (it should not take very long).
6. Copy the ouput from this script to a new query window and then parse the query to verify that the syntax is correct.
7. Once you are satisfied that the query will parse, you can execute it. The length of time it takes to execute will vary depending upon the size of the database, and the capability of the hardware but it may take anything up to 10 or 15 minutes or more. You can see the progress of the script in the results as the script will print out which section it is executing.
8. If you encounter any errors you may need to include any extra object types which prove to be a barrier to making column alterations. DO NOT ATTEMPT TO EDIT THIS SCRIPT UNLESS YOU ARE COMPLETELY FAMILIAR WITH SQL SERVER SYSTEM CATALOG VIEWS AND HOW TO DROP AND CREATE OBJECTS. Also, avoid editing the output from this script.