database design - Change collations of all columns of all tables in SQL Server -

i imported database data compare database.

the target database has collation latin1_general_ci_as , source database has sql_latin1_general_cp1_ci_as.

i did change collation of source database in general latin1_general_ci_as using sql server management studio. tables , columns inside remains old collation.

i know can change column using:

alter table [table]  alter column [column] varchar(100) collate latin1_general_ci_as 

but have tables , columns inside.

before know start write stored procedure reads tables , inside column of type varchar , change them in table , column cursor loop...

does know easier way or way script running through tables in procedure?

as did not find proper way wrote script , i'm sharing here need it. script runs through user tables , collects columns. if column type char type tries convert given collation.

columns has index , constraint free work.

if still has better solution please post it!

declare @collate nvarchar(100); declare @table nvarchar(255); declare @column_name nvarchar(255); declare @column_id int; declare @data_type nvarchar(255); declare @max_length int; declare @row_id int; declare @sql nvarchar(max); declare @sql_column nvarchar(max);  set @collate = 'latin1_general_ci_as';  declare local_table_cursor cursor  select [name] sysobjects objectproperty(id, n'isusertable') = 1  open local_table_cursor fetch next local_table_cursor @table  while @@fetch_status = 0 begin      declare local_change_cursor cursor      select row_number() on (order c.column_id) row_id         , c.name column_name         , t.name data_type         , c.max_length         , c.column_id     sys.columns c     join sys.types t on c.system_type_id = t.system_type_id     left outer join sys.index_columns ic on ic.object_id = c.object_id , ic.column_id = c.column_id     left outer join sys.indexes on ic.object_id = i.object_id , ic.index_id = i.index_id     c.object_id = object_id(@table)     order c.column_id      open local_change_cursor     fetch next local_change_cursor     @row_id, @column_name, @data_type, @max_length, @column_id      while @@fetch_status = 0     begin          if (@max_length = -1) or (@max_length > 4000) set @max_length = 4000;          if (@data_type '%char%')         begin try             set @sql = 'alter table ' + @table + ' alter column ' + @column_name + ' ' + @data_type + '(' + cast(@max_length nvarchar(100)) + ') collate ' + @collate             print @sql             exec sp_executesql @sql         end try         begin catch           print 'error: index or constraint rely on column' + @column_name + '. no conversion possible.'           print @sql         end catch          fetch next local_change_cursor         @row_id, @column_name, @data_type, @max_length, @column_id      end      close local_change_cursor     deallocate local_change_cursor      fetch next local_table_cursor     @table  end  close local_table_cursor deallocate local_table_cursor  go