SQL Server 2008 – How to Change Collation of All Databases

collationsql-server-2008

I have approximately 80 Databases with various collations.

I would like to modify the collation of all 80 databases.

I have the following code:

USE [COLLATE_ADM]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON 
GO

CREATE PROCEDURE [dbo].[PR_COLLATE3] AS
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
    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

This code lists the script to modify all databases (to change table collation, etc.), however I'm facing LOTS of problems, including:

  1. PK is reference of another table.
  2. IX is reference.

So I need to drop EVERYTHING and create again (including functions).

Is there an easy way to do this?

Best Answer

I had to do this some years ago for a SQL Server 2005 DB. It was awful. I've not had to do it on 2008, thankfully, but as far as I know it still works the same way.

Unless specified explicitly, the collation is copied from the next higher object when an object is created. So creating a table will copy the database's collation. Collations are not inherited dynamically at runtime i.e. changing the DB's collation will not change any table's collation.

So you are on the right track. You have to change the collation at every level from column up. Moreover you will almost certainly want to change the collation for the system databases (master, model, msdb and tempdb) otherwise you may get collation conflicts when a query plan uses tempdb and you will have such plans.

Furthermore I remember that the new column collations did not take effect until the data had been written. I ended up performing

update <table>
set <string_column1> = <string_column1>,
    <string_column2> = <string_column2>;

for every table in the database.

If I ever had to do it again I'd install a new instance from scratch with the proper collation, then migrate the tables, then the data, then the constraints. Hopefully you have good source control in place.

Related Question