Once, I was in a situation where I had to change the collation of a database. This is not an easy task, since collations are defined at three levels: Database, Table and Columns. So even, if you change the database collation, all the table and fields will still retain the original collation. Changing collation in all tables and all fields properties could be tedious, but I found a script to do that here. I modified the script though, because it was miscalculating the space taken by multibyte strings and it did not work on databases with special caracters in table and field names.

On top of that script, you’ll have to run a command like the following one to change the database collation:

ALTER DATABASE <database name> COLLATE <collation name>

Example:

ALTER DATABASE [TestsImportation]
COLLATE SQL_Latin1_General_CP1_CI_AI ;

Here is the modified version of the script:

/****** Object:  StoredProcedure [dbo].[LM_ChangeCollation]    Script Date: 02/23/2009 00:15:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
------------------------------------------------------------
-- LM_ChangeCollation - Change collation in all tables
--
--
--
--  made by Luis Monteiro - ljmonteiro@eurociber.pt
--  Modified by Jean-François Beauchamp to support
--  field names containing special characters
--  and to fix a field size bug with double byte types
------------------------------------------------------------
CREATE PROCEDURE [dbo].[LM_ChangeCollation]
@new_collation varchar(100)=NULL
AS

DECLARE
@table            varchar(50),
@column                varchar(60),
@type            varchar(20),
@length            int,
@nullable        varchar(8),
@sql            varchar(8000),
@msg            varchar(8000),
@servercollation    varchar(120)

select @servercollation = convert(sysname, serverproperty('collation'))

DECLARE C1 CURSOR FOR

select
'Table'        = b.name,
'Column'    = a.name,
'Type'        = type_name(a.xusertype),
'Length'    = a.length,
'Nullable'    = case when a.isnullable = 0 then 'NOT NULL' else ' ' end
from syscolumns a, sysobjects b
where a.number = 0
and a.id =b.id
and b.xtype='U'
and b.name not like 'dt%'
and type_name(a.xusertype) in ('char', 'varchar', 'text', 'nchar', 'nvarchar', 'ntext')
order by b.name,a.colid

OPEN C1
FETCH NEXT FROM C1
INTO     @table,@column,@type,@length,@nullable

WHILE @@FETCH_STATUS=0
BEGIN

IF @new_collation is null
set @new_collation=@servercollation

set @sql='ALTER TABLE [' + @table + '] ALTER COLUMN [' + @column + '] '
IF (@type IN ('nchar', 'nvarchar', 'ntext'))
BEGIN
	IF (@length = -1)
		set @sql=@sql+ @type+'(MAX) COLLATE '+@new_collation+' '+@nullable;
	ELSE
		set @sql=@sql+ @type+'(' + CAST((@length / 2) AS VARCHAR(4)) + ') COLLATE '+@new_collation+' '+@nullable;
END
ELSE
	set @sql=@sql+ @type+'(' + CAST(@length AS VARCHAR(4)) + ') COLLATE '+@new_collation+' '+@nullable;

EXEC (@sql)

FETCH NEXT FROM C1
INTO     @table,@column,@type,@length,@nullable

END
CLOSE C1
DEALLOCATE C1