declare 

@NewCollation varchar(255)

,@Stmt nvarchar(4000)

,@DBName sysname

set @NewCollation = 'SQL_Latin1_General_CP1256_CI_AS' -- change this to the collation that you need

set @DBName = DB_NAME()

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+ isnull ('('

+CASE @Size WHEN -1 then 'max' else convert(varchar,@Size) end+')', '') +' 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