Problems change collation ALL ( PART II ) The object '' is dependent on column ''. ( SQL 2008 )

Question

friends.

In a ( Previous Post, we were trying to alter the colation of ALL databases and tables and etc.

This is what i did:

  begin   

DECLARE @collate SYSNAME  
SELECT @collate = 'Latin1_General_CI_AS'           

declare @cmd as nvarchar(4000)             
declare @banco as varchar(100)             

 -- Bancos que serão consultados ( DBS WILL BE CONSULTED )             
Select name into #tmp from master.sys.databases Where name not in ('master', 'model', 'msdb',
 'tempdb')              


 --while   
 -- Loop pelos bancos              
 While (Select count(1) from #tmp) > 0             
  begin         
 Select @banco = min(name) from #tmp             

        set @cmd = '  
 use [' + @banco + ' ]  


        insert into collate_adm.dbo.tblCollateScript  

        SELECT ''?'' as Banco,  
                ''['' + SCHEMA_NAME(o.[schema_id]) + ''].['' + o.name + ''] -> '' + c.name  
              , '' use [' + @banco + ' ] alter database  '+@banco+' set single_user 
                ALTER DATABASE  '+@banco+ '  COLLATE Latin1_General_CI_AS
                ALTER DATABASE '+@banco+' SET MULTI_USER
                             ALTER TABLE     ['' + SCHEMA_NAME(o.[schema_id]) + ''].['' + o.name +
  '']  
                    ALTER COLUMN ['' + c.name + ''] '' +  
                    UPPER(t.name) +  
                    CASE WHEN t.name NOT IN (''ntext'', ''text'')  
                         THEN ''('' +  
                               CASE  
                                     WHEN t.name IN (''nchar'', ''nvarchar'') AND c.max_length !=
 -1  
                                           THEN CAST(c.max_length / 2 AS VARCHAR(10))  
                                     WHEN t.name IN (''nchar'', ''nvarchar'') AND c.max_length = 
 -1  
                                           THEN ''MAX''  
                                     ELSE CAST(c.max_length AS VARCHAR(10))  
                               END + '')''  
                         ELSE ''''  
                    END + '' COLLATE Latin1_General_CI_AS '' +  
                    CASE WHEN c.is_nullable = 1  
                         THEN '' NULL''  
                         ELSE '' NOT NULL''  
                    END  
        FROM sys.columns c WITH(NOLOCK)  
        JOIN sys.objects o WITH(NOLOCK) ON c.[object_id] = o.[object_id]  
        JOIN sys.types t WITH(NOLOCK) ON c.system_type_id = t.system_type_id AND c.user_type_id =
  t.user_type_id  
        WHERE t.name IN (''char'', ''varchar'', ''text'', ''nvarchar'', ''ntext'', ''nchar'')  
                               AND o.[type] = ''U''  
        '  



  set @cmd = replace(replace(@cmd,'?', @banco) , 'XXXcollateXXX', @collate)  


  begin try             
        exec sp_executeSQL @cmd -- Executa comando gerado pelo script             
  end try    
  begin catch             
        insert into tblCollateScript (rotina, script,Data) values ('pr_BuscaCotas', @cmd, 
GETDATE())              
  end catch                    

 Delete from #tmp Where name = @banco             
 end             

 drop table #tmp             
end      

GO

It will show me something like this:

 use [br_teste ] alter database  br_teste set single_user                       ALTER DATABASE   
 br_teste  COLLATE Latin1_General_CI_AS                      ALTER DATABASE br_teste SET   
 MULTI_USER                                   ALTER TABLE     [dbo.]
 [Participants_BR140515850101_HistoryTable]                            ALTER COLUMN [SampleId] 
 NVARCHAR(64) COLLATE Latin1_General_CI_AS  NULL

This will loop through ALL databases and ALL tables. This is what i want.

BUT i'm finding this error sometimes:

Msg 5074, Level 16, State 1, Line 112 The object 'IX_CTTrace_Unique' is dependent on column 'TraceName'. Msg 4922, Level 16, State 9, Line 112 ALTER TABLE ALTER COLUMN TraceName failed because one or more objects access this column.

How can i fight this? because there is a LOT of this messages and we have hunderds os databases and tables and Pks...

It is the final line to the victory !! my boss wil finally say "hey...good job my friend..."

There is this answer i found:

Ahh, this is one of the worst problems in SQL Server: you cannot change the collation once an object is created (this is true both for tables and databases...). You can only save your data (don't use bcp or backup utilities, you need to place them in a csv of similar file types...), drop the database, recreate with the right collation and re-import the data into the new database... Hope this helps, Bye cghersi

I don't want to give up. 2 months DBA and i want to be awesome.

Thanks friends !