I know this is an old post, but as it is also a very useful one, I will add that I needed to add a short loop at the front to turn off all check constraints, and a short one at the end to re-enable them. One does not need to list each constraint, only the tables effected

select distinct 'ALTER TABLE .[dbo].['+rtrim(P.name) +'] CHECK CONSTRAINT all'

from [INFORMATION_SCHEMA].[CHECK_CONSTRAINTS] C

join sys.sysobjects O on name=CONSTRAINT_NAME

join sys.sysobjects P on P.id=O.parent_obj

and

select distinct 'ALTER TABLE .[dbo].['+rtrim(P.name) +'] NOCHECK CONSTRAINT all'

from [INFORMATION_SCHEMA].[CHECK_CONSTRAINTS] C

join sys.sysobjects O on name=CONSTRAINT_NAME

join sys.sysobjects P on P.id=O.parent_obj

Also, since most collation changes are "TO" the instance collation, I added that at the front.

DECLARE @InstanceCollation sysname

SET @InstanceCollation = CAST( DATABASEPROPERTYEX ( 'tempdb', 'Collation' ) AS sysname)

set SET @NewCollation = @InstanceCollation