ALTER DATABASE ... ALTER COLLATION (FORCED).
As all of you know Microsoft has made a small problem for all SQL DBA and developers. If you have a database with any of SQL_xxxxx collations (for more details please have a look at the TERTIARY_WEIGHTS() function here), you will defenetily have a significant problem with index search and ordering performance if you'll try to upgrade your SQL Server with 2005 version. Some of developers was excited when first time reading this article, really :-).
Suddenly ALTER DATABASE .. ALTER COLLATION command will change database collation for you, but all objects will stay "as is" with old collation (by design). The only way to change collation for all existing objects is to create a new empty database with new desired collation and copy data into this new database (copy wizzard, for example).
To sort this issue out i created this small script (in attachment). It was tested both manually and with using VSTS DBPro, and i can confirm that you can change the collation of your database without any problem and manual work. As an input you should provide it with database with one collation, and as output you will have the same database with another (desired) collation. So as for me this is an analoque of ALTER DATABASE ... ALTER COLLATION FORCED command :-). How it works:
- you should use sqlcmd or SSMS in sqlcmd mode,
- specify database name as a parameter,
- specify desired collation as a parameter,
- run it and wait for results.
What is not covered by the script: table and index partitioning. I guess that if you are using these advanced options, you are smart enough to change this script to add required feature. Mostly i created this script for the following scenario: (1) detach database from SQL Server 6.5/7.0/200 version; (2) attach it to SQL Server 2005; (3) run this script; (4) use your database asap.
Some words about performance: than more tables with computed columns (computed column not at the end of the table) you have than more time you will have to wait. 100 Gigabyte db can be easily transformed within one hour on AI64 4way with EVA8000.
Also it is possible to use this script to study SQL 2005 system views and relationship between them.
Many thanks for any reply and comments.
---- January 2008
Many thanks to WaitForPete, script updated to fix these (and some other) issues.
Comments
Anonymous
December 02, 2006
Will this allow me to go through and reset the collation on every single field in the database to the database default??? It's killing me what SQL Server does changing fields randomly all of the time!Anonymous
December 02, 2006
Sure, JohnGalt. This script will go through every field in database and will change field collation. Even if this field is a member of primary constraint, has referensed by caclulated field, indexed, referensed by foreign key constraint.. whatever you want :-)Anonymous
March 22, 2007
Hi Igor, Thanks for a great script. What a timesaver! The script seems to have a problem scripting VarChar(MAX) columns. The script ends up looking like this... ALTER TABLE [dbo].[tblJobFinderSearch] ALTER COLUMN [ClientLink] varchar(-1) COLLATE DATABASE_DEFAULT NULL Notice the (-1) for the varchar setting? This column used to be VarChar(Max). Any ideas? Thanks Rod.Anonymous
March 22, 2007
Here's some other things to watch out for.... -- Make a backup of the database first!!! If this script fails, it WILL leave the database in a half-repaired state where it may be missing all of the indexes, primary keys, constraints and other objects -- Change any VarChar(MAX) columns back to VarChar(n) and then change back to MAX after running. -- Remove any Full Text Catalogs before running this script -- Run DBCC CHECKDB to identify any database errors or inconsistencies -- Run DBCC UPDATEUSAGE(0) on database first, especially if the database has been upgraded from SQL Server 2000 to 2005 Thanks, RodAnonymous
March 22, 2007
Rod, thanks a lot for your comment. We've identified issue with SQL Server collations during consolidation server process + migration from SQL 2000 to 2005 version. As far as SQL 2000 does not implement char(max) datatype these (any of max) datatypes are not covered by the script. You are absolutely right with Full Text Catalog feature - it might be usefull approach to drop and recreate all catalogues. Please also pay attention on partitioned tables and indexes - partitioning ans partitioned fuctions are also doesn't covered by the script. Hopefully you can use this script as a template to add any additional functions as you wish.Anonymous
May 01, 2007
Do you have the same script but for SQL 2000Anonymous
May 01, 2007
No. It's to complicated for me because of lack of (n)varchar(max) data type. But you can try to look for AlterCollation.exe program (i saw this freeware tool with sources for SQL 2000). During testing i found a few bugs in it, but it is better then nothing.Anonymous
May 17, 2007
Thanks a lot , saved me a fair bit of time. Thought you should know there is a bug in the script if you are running it on a database which is already case sensitive from SSMS The statement DECLARE cviews should be changed to DECLARE cViewsAnonymous
May 17, 2007
John, thank you. I'll try to improve the script next time when i meet with case sensitive.Anonymous
November 30, 2007
Thank you, I downloaded your script and it ran flawlessly, everything seems to be ok and collated the same way.Anonymous
December 18, 2007
Hi Igor this script is most useful for our SQL 2005 customers who have fallen foul of this trap, but there is NO sign of the AlterCollation.exe program that you mentioned for SQL 2000. I am having a look at porting the script back to 2000 but am floundering with the new system tables that you are using. e.g. this clause crops up repeatedly FROM sys.sql_modules m JOIN sysobjects o on o.[objectid] = m.[objectid] join sys.schemas s on s.schema_id = o.schema_id WHERE ( m.uses_database_collation = 1 or m.is_schema_bound = 1 ) but I haven't yet found a SQL 2000 equivalent for the uses_database_collation or is_schema_bound columns. Obviously it would be far more efficient to locate the program, but right now the only search result that comes up is this one! I did also look in the SQL 2000 media but it looks like that is not what you meant. Any pointers most gratefully received. PeteAnonymous
December 18, 2007
Pete, Suddenly i don't have SQL 2000 nearby me, mostly SQL 2008 :-). I may firget something, but i think you can't find the analoque in SQL 2000. As for me it will be much easier to drop/recreate ALL stored procedures / functions / views instead of analysing column set. This will (1) simplify script (2) speedup processing. IgorAnonymous
December 18, 2007
I am allowed to drop support forSQL 2000 soon, in the meantime.... We have the drop and recreate script for all the programability objects anyway so it looks like sharp scissors time for that section. Thanks PeteAnonymous
January 15, 2008
PS, I did find one bug on the way through. The lines IF @delete_referential_action = 1 SET @stmt = @stmt + ' NOT FOR REPLICATION' , appearing in a couple of locations, are incorrect, they should be IF @is_not_for_replication = 1 SET @stmt = @stmt + ' NOT FOR REPLICATION'Anonymous
January 24, 2008
GREAT SCRIPT! Also -- I had to add a few lines: Alter the order of the FK Constraints from: ON UPDATE CASCADE NOT FOR REPLICATION ON DELETE CASCADE to ON UPDATE CASCADE ON DELETE CASCADE NOT FOR REPLICATION to avoid syntax problems. Added: , cc.is_not_for_replication to SELECT cc.[name] , par_obj_name = o.[name] , owner = s.[name] , cc.definition , [with_check] = case cc.is_not_trusted when 1 then 'WITH NOCHECK' else '' end , cc.is_not_for_replication FROM sys.check_constraints cc join sys.objects o on o.[object_id] = cc.parent_object_id join sys.schemas s on s.schema_id = o.schema_id to get "NOT FOR REPLICATION" into the check constraints Added: SET @obj_def = CASE WHEN left(@obj_def, 1) = '(' THEN right(@obj_def, len(@obj_def) - 1) END SET @obj_def = CASE WHEN right(@obj_def, 1) = ')' THEN left(@obj_def, len(@obj_def) - 1) END to remove the double parenthesis that the default constraints get after the script. Otherwise - GREAT SCRIPT! Thanks much! DavidAnonymous
April 03, 2008
Thank you for sharing your work Igor. This was a great timesaver!Anonymous
April 10, 2008
Hi, the script does not work for me converting Lithuanian_CI_AS to Latin1_General_CI_AI All Lithuanian letters look incorrect Great pity. Glad it worked for others. Thanks for sharing, anyway.Anonymous
April 15, 2008
The comment has been removedAnonymous
May 09, 2008
I have found that you can easily modify this script to support database names with strange characters in the name by replacing all of the instances of "$(destdb)" with "[$(destdb)]" (excluding the quotes of course) except for the use in the following query. It should remain as shown here. SELECT @RecoveryModel = recovery_model FROM sys.databases WHERE [name] = '$(destdb)'Anonymous
May 16, 2008
The comment has been removedAnonymous
June 03, 2008
I see back in March 2007 Steeve Gauvreau asked if there was a SQL 2000 equivalent script. Has anyone found such a script yet?Anonymous
September 21, 2008
OMG - You rock! This was awesome, not so painful and complicated as I thought! I am no beginner with Sql Server but I am with 2005, so, I had some issues figuring out the sqlcmd, so incase anyone else needs a sample: from dos prompt sqlcmd -S your_server_name -U sa -P your_sa_password -i "change_collation.sql" -o "change_collation.out"Anonymous
October 15, 2008
That is a great script. Thank you!Anonymous
October 20, 2008
this saved me a lot of hours, really thank you for your work. worked flawlessly on SQL Server 2005 SP2 64-Bit.Anonymous
October 28, 2008
The comment has been removedAnonymous
November 14, 2008
This script worked great for me on SQL 2005 server and fixed the issue that I plan the big effort on othe way. I really appreciate your work.Anonymous
February 05, 2009
Great work. It is people like you that keep people like me in a job. CheersAnonymous
February 18, 2009
Works flawlessly on my databases that have hundreds of columns with all sorts of constraints, etc. Thank you very much for saving me the trouble and time. Really appreciate your sharing this.Anonymous
March 04, 2009
Many thanks your script worked a treat on a database I'm migrating.Anonymous
June 05, 2009
Terrific job. Congratulations. Thx.Anonymous
July 07, 2009
Excelent script!! Thanks for sharing!Anonymous
August 11, 2009
Excelent script!!!!! I really appreciate your sharing this.Anonymous
September 13, 2009
Hi - Great script!! Couldn't face scripting out all the dependant objects. Loving the script only option too. Just one change I've made: In the "start changing collation" section, so that collation isn't changed if you're only interested in the scripting: IF $(script_only) = 0 ALTER DATABASE $(destdb) COLLATE $(desired_collation) Thanks again SteveAnonymous
September 13, 2009
Sorry one more changes - you want to drop the tempdb tables when you're scripting also so have commented out the "IF $(script_only) = 0" in the "prepare temporary tables" section. Again, many thanks!!Anonymous
January 11, 2010
The comment has been removedAnonymous
February 09, 2010
In the -- drop check constraints section, I don't think the code is right when it completes the dropping, as it always reports PRINT '-- DROP CHECK CONSTRAINTS failed' I think this is because :- -- **************************************************************** -- drop check constraints -- **************************************************************** PRINT '-- ***' PRINT '-- DROP CHECK CONSTRAINTS' IF NOT EXISTS(SELECT TOP 1 1 FROM tempdb.dbo.[check_constr]) GOTO SKIP_DROP_CHECK_CONSTR DECLARE ms_cc_cursor CURSOR FOR SELECT [name], [owner], par_obj_name FROM tempdb.dbo.[check_constr] open ms_cc_cursor fetch next from ms_cc_cursor into @obj_name, @owner, @table_name WHILE @@fetch_status >= 0 BEGIN SET @stmt = 'ALTER TABLE [' + @owner + '].[' + @table_name + '] DROP CONSTRAINT [' + @obj_name + ']' PRINT @stmt IF $(script_only) = 0 EXEC(@stmt) IF @@error <> 0 BEGIN PRINT '-- DROP CHECK CONSTRAINT FAILED. SEE ERROR LOG FOR DETAILS.' RAISERROR('DROP CHECK CONSTRAINT FAILED. SEE ERROR LOG FOR DETAILS.', 16, 1) RETURN END fetch next from ms_cc_cursor into @obj_name, @owner, @table_name END -- close cursor CLOSE ms_cc_cursor DEALLOCATE ms_cc_cursor SKIP_DROP_CHECK_CONSTR: PRINT '-- DROP CHECK CONSTRAINTS failed' -- **************************************************************** -- drop indexes -- **************************************** After the DEALLOCATE ms_cc_cursor you don't jump the SKIP_DROP_CHECK_CONSTR: element and therefore always print a failure. Also, is there any chance a summary could be printed at the bottom of the log??Anonymous
February 09, 2010
hi, I was wondering if this script also works on a SQL Server 2008? TIA Stefaan.Anonymous
April 01, 2010
Great script! Excellent work. Many thanks.Anonymous
April 20, 2010
The comment has been removedAnonymous
April 20, 2010
Do not try to run the script on multiple databases at the same time. The use of "tempdb.dbo." will create the temporary tables in the system database tempdb. When working on two databases at the same time the table will already be created and all things go wrong from that point. Replacing "temp.dbo." with a # will create a real temporary table for each opened query window. Without dropping the temporary tables however, the script will not run in the same query window after editing the databasename to work on (destdb).Anonymous
August 12, 2010
The comment has been removedAnonymous
June 20, 2011
Very useful! Thanks. Just to let you know, I have made some little changes:
- replaced SET @stmt = @stmt + '(' + cast(@length as varchar(4)) + ')' with SET @stmt = @stmt + '(' + case when @length <= 0 then 'max' else cast(@length as varchar(4)) end + ')' as suggested by WaitForPete (note the <= instead of just =)
- dropped manually unique filtered index, as they are not supported by the script
Anonymous
April 27, 2012
Hey Igor, Will this script work for a SQL 2008 db that is being moved to SQL 2012? I'll admit that I have only scanned the script at this time. I plan to dig into it soon, but if you or someone else knows the answer to this or can give me some direction that would be great. Thanks, LandonAnonymous
August 23, 2012
Igor, this script is fantastic, thank-you. It worked well for me when moving from 2008 to 2008 R2. After several failures that left the database in an unusable state, I decided I'd rather have the script do everything it can and then clean up the errors myself, afterwards. To do this, I commented out the RAISERROR and RETURNs after failures (there were 71 total). I anticipated having to fix the root cause of the issues that came up and then rerun the script, but I was lucky; all of the errors I fixed after the fact by simply tweaking and rerunning the SQL. Thanks again Igor.Anonymous
December 12, 2013
Thanks a lot .. Anybody can use it . I approve ...Anonymous
April 10, 2014
change_collation.sql, this script worked even for SQL Server 2012. My requirement was to convert db collation Russian and it worked. The script is 8 years old and still going. Great Job Thank youAnonymous
September 10, 2014
This is the best script to convert collation. The problem of VarChar(MAX) columns, i fix it with: ... [length] varchar NULL, ... , length = case when cast(c.max_length as varchar(4)) like '-1' then 'MAX' else cast(c.max_length as varchar(4)) end ... Thanks a LOTAnonymous
November 19, 2014
Actually there are plenty of things in this script that should be fixed. when column type is varchar and size is -1 that means that the column is Text. Also when type is varchar and size 0, that means that the column type is nvarchar and size max. Follows update: [code] PRINT '-- ***' PRINT '-- COLUMNS COLLATION IN PROGRESS...' IF NOT EXISTS(SELECT TOP 1 1 FROM tempdb.dbo.char_cols) GOTO SKIP_DROP_CHAR_COLS DECLARE ms_col_cursor CURSOR FOR select owner, table_name, [col_name], length, type_name, nullable, is_user_defined from tempdb.dbo.char_cols open ms_col_cursor fetch next from ms_col_cursor into @owner, @table_name, @col_name, @length, @type_name, @nullable, @flag WHILE @@fetch_status >= 0 BEGIN IF @type_name='varchar' AND @length=-1 BEGIN SET @type_name='text' SET @length=0 END IF @type_name='varchar' AND @length=0 BEGIN SET @type_name='nvarchar' SET @length='MAX' END SET @stmt = 'ALTER TABLE [' + @owner + '].[' + @table_name + '] ALTER COLUMN [' + @col_name + '] ' + @type_name -- length IF NOT (UPPER(@type_name COLLATE DATABASE_DEFAULT) LIKE '%TEXT' OR UPPER(@type_name COLLATE DATABASE_DEFAULT) = 'SYSNAME' OR @flag = 1) SET @stmt = @stmt + '(' + cast(@length as varchar(4)) + ')' IF @flag = 0 SET @stmt = @stmt + ' COLLATE DATABASE_DEFAULT ' SET @stmt = @stmt + ' ' + @nullable PRINT @stmt IF $(script_only) = 0 EXEC(@stmt) IF @@error <> 0 BEGIN PRINT '-- ALTER COLUMN FAILED. SEE ERROR LOG FOR DETAILS.' RAISERROR('ALTER COLUMN FAILED. SEE ERROR LOG FOR DETAILS.', 16, 1) RETURN END fetch next from ms_col_cursor into @owner, @table_name, @col_name, @length, @type_name, @nullable, @flag END -- close cursor [/code]