Is it possible to change collation for all objects under the database?
For example, if I change the database collation, it should change all the objects (tables,procs,etc.) related to that database.
I had this issue a while ago when we changed the underlying database to support unicode and needed to change character sets on a legacy database to support hungarian.
This script will get you a long way to changing the collation; you will need to change the main DB collation manually. I haven't had to run it in a long while it doesn't fix any calculated columns but there may be other issues. Don't run it in a live database without testing it first - you might want to select out the changes it makes so you can audit or figure out the ones missed later.
Declare @NewCollation varchar(255), @DBName sysname Select @NewCollation = 'Latin_1_CI_AI', -- change this to the collation that you need @DBName = DB_NAME() Declare @CName varchar(255), @TbleName sysname, @objOwner sysname, @Sql varchar(8000), @Size int, @Status tinyint, @Colorder int Declare CurWhileLoop 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 CurWhileLoop SET XACT_ABORT ON begin tran fetch CurWhileLoop into @CName, @TbleName, @objOwner, @Size, @Status, @Colorder while @@FETCH_STATUS =0 begin set @Sql='ALTER TABLE '+@objOwner+' ALTER COLUMN '+@CName+' '+@TbleName+ isnull ('(' +convert(varchar,@Size)+')', '') +' 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 fetch CurWhileLoop into @CName, @TbleName, @objOwner, @Size, @Status, @Colorder end close CurWhileLoop deallocate CurWhileLoop commit tran