------------------------------------------------------------
-- LM_ChangeCollation - Change collation in all tables
--
--
--
--  made by Luis Monteiro - ljmonteiro@eurociber.pt 
--  Patched 20100512 - Rich James (rich reallyat richjames,me)
--  (Fixed text/ntext and n{var}char bugs)
------------------------------------------------------------
CREATE PROCEDURE [LM_ChangeCollation]
@new_collation varchar(100)=NULL,
@mode char(4)=NULL,
@fixcollate varchar(100)=NULL
 AS

DECLARE 
@table			varchar(50),
@column		        varchar(60),
@type			varchar(20),
@length			varchar(4),
@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'	= CASE
					WHEN type_name(a.xusertype)='nchar' 
					or 
						type_name(a.xusertype)='nvarchar'
					THEN (a.length/2)
					ELSE
						a.length
				  END,
	'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', 'nchar', 'nvarchar','text','ntext')
and a.collation like coalesce(@fixcollate,'%')

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 + '] ' + @type
if (@type<>'text' AND @type<>'ntext') 
	set @sql=@sql+'('+@length+')'
set @sql=@sql+' COLLATE '+@new_collation+' '+@nullable
IF (@mode='live')
	EXEC (@sql)
ELSE
	print @sql
FETCH NEXT FROM C1
INTO 	@table,@column,@type,@length,@nullable

END
CLOSE C1
DEALLOCATE C1
GO