Sql-server – How to change the collation of a SQL Server Database

I'm trying to standardise all databases on a single collation – Latin1_General_CI_AS (the standard collation). I have some databases that are in SQL_Latin1_General_CP1_CI_AS.

I know I can use ALTER DATABASE to change the database collation, but that only affects new objects. My understanding is that the only way to change existing columns is to do ALTER COLUMN on each column in every table – and I would need to drop and recreate all the indexes to do even that.

I guess it would look something like this:

DROP INDEX indexname ON tablename

GO

ALTER TABLE tablename ALTER COLUMN columname varchar(50) COLLATE Latin1_General_CI_AS NULL

GO

CREATE CLUSTERED INDEX indexname ON tablename (columname ASC)

and repeat for every varchar, char, text, nvarchar, nchar and ntext column in the entire database. That would be an enormous SQL script.

Is there an easier way to do this, or can anyone suggest a way to automate the creation of a SQL script to do it?