UMG Developer (3/15/2011)
I think this article is a great start, but it ends too early, just using ALTER DATABASE only changes the default collation, it doesn't change existing tables, columns, or the data in the tables.
Good pick up!
Tables within each DB will not only keep their old collation for pre-existing columns, but newly created columns will have the new server collation, leading to a nightmare scenario of mixed collations within the same table.
I can contribute this script, the major part of it borrowed from another SQLServerCentral contributor (sorry I did not preserve the name!). I tested it on a few databases and think it will work but - as usual - test it first !!!!!!
ALTER PROCEDURE [dbo].[usp_CollationChangeAllTables]
as
--USAGE: USE THIS DB ------- very very important
--USE thisDB
-- EXEC DBA..usp_CollationChangeAllTables
begin
--ALTER TABLE dbo.tblXXX DROP CONSTRAINT
--PK & FK are not character columns so they can stay
declare
@NewCollation varchar(255)
,@Stmt nvarchar(4000)
set @NewCollation = 'Latin1_General_CI_AS' -- change this to the collation that you need
--WAS 'SQL_Latin1_General_CP1_CI_AS'
declare
@CName varchar(255)
,@TName sysname
,@OName sysname
,@Sql varchar(8000)
,@Size int
,@Status tinyint
,@Colorder int
declare curcolumns cursor read_only forward_only local
for
select
QUOTENAME(C.Name)
,T.Name
,QUOTENAME(U.Name) + '.' +QUOTENAME(O.Name)
,C.Prec
,C.isnullable
,C.colorder
from syscolumns C
inner join systypes T on C.xtype=T.xtype
inner join sysobjects O on C.ID=O.ID
inner join sysusers u on O.uid = u.uid
where T.Name in ('varchar', 'char', 'text', 'nchar', 'nvarchar', 'ntext')
and O.xtype in ('U')
and C.collation != @NewCollation
and objectProperty(O.ID, 'ismsshipped')=0
order by 3, 1
open curcolumns
SET XACT_ABORT ON
begin tran
fetch curcolumns into @CName, @TName, @OName, @Size, @status, @Colorder
while @@FETCH_STATUS =0
BEGIN
set @Sql='ALTER TABLE '+@OName+' ALTER COLUMN '+@CName+' '+@TName
SET @Sql = @Sql + CASE WHEN @Size = -1 THEN + '(MAX)' ELSE isnull ('('+convert(varchar,@Size)+')', '') END
SET @Sql = @Sql +' COLLATE '+ @NewCollation +' '+case when @status=1 then 'NULL' else 'NOT NULL' end
exec(@Sql) -- change this to print if you need only the script, not the action
PRINT @Sql
fetch curcolumns into @CName, @TName, @OName, @Size, @status, @Colorder
end
close curcolumns
deallocate curcolumns
commit tran
END --proc
See also
(Keep in mind TheSQLGuru's warning, all these steps will change the collation for the server, the databases default, then the table defs and will guarrantee consistency for the future, but it is not clear what happens to the data already existing in each table.)