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!

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.

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,

Rod

Do you have the same script but for SQL 2000

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 cViews

Thank you, I downloaded your script and it ran flawlessly, everything seems to be ok and collated the same way.

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.

Pete

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

Pete

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'

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!

David

Thank you for sharing your work Igor. This was a great timesaver!

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.

I've tested the script on my CRM 3.0 datbase (backup). But the script fails on

ALTER TABLE [dbo].[fn_CollectForCascadeAssign] DROP CONSTRAINT [DF__fn_Collec__proce__5A8539BC]

Msg 4902, Level 16, State 1, Server PCSTEST1, Line 1

Cannot find the object "dbo.fn_CollectForCascadeAssign" because it does not exist or you do not have permissions.

-- DROP DEFAULT CONSTRAINT FAILED. SEE ERROR LOG FOR DETAILS.

Msg 50000, Level 16, State 1, Server PCSTEST1, Line 1245

It seems that the "Table-valued Functions" is deleted somewhere in the script.

If anyone could fix this problem I would be very thankful.

regards

bjorn

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)'

Thanks a lot Igor.  Your really well done script saved my butt.

Only issue I had was a user defined fonction using a varchar(8000), changed it before running the script and all was fine.

With a database compare I also noticed an index drop a desc clause on a date field.

I see back in March 2007 Steeve Gauvreau asked if there was a SQL 2000 equivalent script.  Has anyone found such a script yet?

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"

That is a great script. Thank you!

this saved me a lot of hours, really thank you for your work.

worked flawlessly on SQL Server 2005 SP2 64-Bit.

Really nie.

Worked fine, except I got errors in the very last section of the script.

Msg 16916, Level 16, State 1, Line 3247

A cursor with the name 'cViews' does not exist.

Msg 16916, Level 16, State 1, Line 3253

A cursor with the name 'cViews' does not exist.

Msg 16916, Level 16, State 1, Line 3276

A cursor with the name 'cViews' does not exist.

Msg 16916, Level 16, State 1, Line 3277

A cursor with the name 'cViews' does not exist.

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.

Great work. It is people like you that keep people like me in a job.

Cheers

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.

Many thanks your script worked a treat on a database I'm migrating.

Terrific job. Congratulations.

Thx.