Help Changing Collate of ALL databases

Question

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?

• LowlyDBA - John M: What version of SQL Server are you using?
• Racer SQL: Sorry, Im using SQL 2008 Ent.
• Hannah Vernon: Are you certain you need to modify the collation of each table? Modifying the collation for objects inside the database is only necessary if the collation has been specifically set on that object.
• Racer SQL: The problem is, I'm Dba for 3 Months. I'm DBA Jr. This is out of my knowledge. Im telling my boss that this isnt so simple. Its dangerous do mess with constraints and functions. But he want it...he want all tables, databases with same collation ( latin1_general_ci_as ).
• Racer SQL: I made some scripts do drop all pks, fks and default constraints, and another one to recreate them. but, i dont want to do this. Im using it in my personal database for test and the database is, now, really messed up.

Answer 1

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.

• Racer SQL: Thank you very much. And yes I'm trying to change the collation based on our master. I see lots of tables using <default_database_collation>. I will do some more research and post here something more usefull. I will try to post scripts i'm using ( create/drop PF\FK\DF keys )Thank you for the help.