Change collations of all columns of all tables in SQL Server

Question

I imported a database with some data to compare with another database.

The target database has collation Latin1_General_CI_AS and the source database has SQL_Latin1_General_CP1_CI_AS.

I did change the collation of the source database in general to Latin1_General_CI_AS using the SQL Server Management Studio. But the tables and columns inside remains with the old collation.

I know that I can change a column using:

ALTER TABLE [table] 
ALTER COLUMN [column] VARCHAR(100) COLLATE Latin1_General_CI_AS

But I have to do this for all tables and all columns inside.

Before I know start to write a stored procedure that reads all tables and inside all column of type varchar and change them in a table and column cursor loop...

Does anyone know an easier way or is the only way to do this with a script running through all tables in a procedure?

Answer 1

As I did not find a proper way I wrote a script to do it and I'm sharing it here for those who need it. The script runs through all user tables and collects the columns. If the column type is any char type then it tries to convert it to the given collation.

Columns has to be index and constraint free for this to work.

If someone still has a better solution to this 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 FOR

SELECT [name]
FROM sysobjects
WHERE OBJECTPROPERTY(id, N'IsUserTable') = 1

OPEN local_table_cursor
FETCH NEXT FROM local_table_cursor
INTO @table

WHILE @@FETCH_STATUS = 0
BEGIN

    DECLARE local_change_cursor CURSOR FOR

    SELECT ROW_NUMBER() OVER (ORDER BY c.column_id) AS row_id
        , c.name column_name
        , t.Name data_type
        , c.max_length
        , c.column_id
    FROM 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 AND ic.column_id = c.column_id
    LEFT OUTER JOIN sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
    WHERE c.object_id = OBJECT_ID(@table)
    ORDER BY c.column_id

    OPEN local_change_cursor
    FETCH NEXT FROM local_change_cursor
    INTO @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 LIKE '%char%')
        BEGIN TRY
            SET @sql = 'ALTER TABLE ' + @table + ' ALTER COLUMN ' + @column_name + ' ' + @data_type + '(' + CAST(@max_length AS nvarchar(100)) + ') COLLATE ' + @collate
            PRINT @sql
            EXEC sp_executesql @sql
        END TRY
        BEGIN CATCH
          PRINT 'ERROR: Some index or constraint rely on the column' + @column_name + '. No conversion possible.'
          PRINT @sql
        END CATCH

        FETCH NEXT FROM local_change_cursor
        INTO @row_id, @column_name, @data_type, @max_length, @column_id

    END

    CLOSE local_change_cursor
    DEALLOCATE local_change_cursor

    FETCH NEXT FROM local_table_cursor
    INTO @table

END

CLOSE local_table_cursor
DEALLOCATE local_table_cursor

GO

Answer 2

So here I am, once again, not satisfied with the answer. I was tasked to upgrade JIRA 6.4.x to JIRA Software 7.x and I went to that particular problem with the database and column collation.

In SQL Server, if you do not drop constraints such as primary key or foreign key or even indexes, the script provided above as an answer doesn't work at all. It will however change those without those properties. This is really problematic, because I don't want to manually drop all constraints and create them back. That operation could probably end up with errors. On the other side, creating a script automating the change could take ages to make.

So I found a way to make the migration simply by using SQL Management Studio. Here's the procedure:

• Rename the database to something else. For example, mine was "Jira", so I renamed it "JiraTemp".
• Create a new database named "Jira" and make sure to set the right collation. Simply select the page "Options" and change the collation.
• Once created, go back to "JiraTemp", right click it, "Tasks -> Generate Scripts...".
  • Select "Script entire database and all database objects".
  • Select "Save to new query window", then select "Advanced"
  • Change the value of "Script for Server Version" to the desired value
  • Enable "Script Object-Level Permissions", "Script Owner" and "Script Full-Text Indexes"
  • Leave everything else as is or personalize it if you wish.
• Once generated, delete the "CREATE DATABASE" section. Replace "JiraTemp" with "Jira".
• Run the script. The entire database structure and permissions of the database is now replicated to "Jira".
• Before we copy the data, we need to disable all constraints. Execute the following command to do so in the database "Jira": EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
• Now the data needs to be transferred. To do so, simply right click "JiraTemp", then select "Tasks -> Export Data..."
  • Select as data source and destination the OLE DB Provider for SQL Server.
  • Source database is "JiraTemp"
  • Destination database is "Jira"
  • The server name is technically the same for source and destination (except if you've created the database on another server).
  • Select "Copy data from one or another tables or views"
  • Select all tables except views. Then, when still highlighted, click on "Edit Mappings". Check "Enable identity insert"
  • Click OK, Next, then Finish
• Data transfer can take a while. Once finished, execute the following command to re enable all constraints: exec sp_msforeachtable @command1="print '?'", @command2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

Once completed, I restarted JIRA and my database collation was in order. Hope it helps a lot of people!

Answer 3

Fixed length problem nvarchar and added NULL/NOT NULL

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);
DECLARE @is_Nullable bit;
DECLARE @null nvarchar(25);

SET @collate = 'Latin1_General_CI_AS';

DECLARE local_table_cursor CURSOR FOR

SELECT [name]
FROM sysobjects
WHERE OBJECTPROPERTY(id, N'IsUserTable') = 1

OPEN local_table_cursor
FETCH NEXT FROM local_table_cursor
INTO @table

WHILE @@FETCH_STATUS = 0
BEGIN

    DECLARE local_change_cursor CURSOR FOR

    SELECT ROW_NUMBER() OVER (ORDER BY c.column_id) AS row_id
        , c.name column_name
        , t.Name data_type
        , c.max_length
        , c.column_id
        , c.is_nullable
    FROM 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 AND ic.column_id = c.column_id
    LEFT OUTER JOIN sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
    WHERE c.object_id = OBJECT_ID(@table)
    ORDER BY c.column_id

    OPEN local_change_cursor
    FETCH NEXT FROM local_change_cursor
    INTO @row_id, @column_name, @data_type, @max_length, @column_id, @is_nullable

    WHILE @@FETCH_STATUS = 0
    BEGIN

        IF (@max_length = -1) SET @max_length = 4000;
        set @null=' NOT NULL'
        if (@is_nullable = 1) Set @null=' NULL'
        if (@Data_type='nvarchar') set @max_length=cast(@max_length/2 as bigint)
        IF (@data_type LIKE '%char%')
        BEGIN TRY
            SET @sql = 'ALTER TABLE ' + @table + ' ALTER COLUMN [' + rtrim(@column_name) + '] ' + @data_type + '(' + CAST(@max_length AS nvarchar(100)) +  ') COLLATE ' + @collate + @null
            PRINT @sql
            EXEC sp_executesql @sql
        END TRY
        BEGIN CATCH
          PRINT 'ERROR: Some index or contraint rely on the column ' + @column_name + '. No conversion possible.'
          PRINT @sql
        END CATCH

        FETCH NEXT FROM local_change_cursor
        INTO @row_id, @column_name, @data_type, @max_length, @column_id, @is_Nullable

    END

    CLOSE local_change_cursor
    DEALLOCATE local_change_cursor

    FETCH NEXT FROM local_table_cursor
    INTO @table

END

CLOSE local_table_cursor
DEALLOCATE local_table_cursor

GO

Answer 4

I made a little change on the script.

DECLARE @collate nvarchar(100);
DECLARE @table sysname;
DECLARE @schema sysname;
DECLARE @objectId int;
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);
DECLARE @is_Nullable bit;
DECLARE @null nvarchar(25);

SET @collate = 'Latin1_General_CI_AS';

DECLARE local_table_cursor CURSOR FOR

SELECT tbl.TABLE_SCHEMA,[name],obj.id
FROM sysobjects as obj
inner join INFORMATION_SCHEMA.TABLES as tbl
on obj.name = tbl.TABLE_NAME
WHERE OBJECTPROPERTY(obj.id, N'IsUserTable') = 1

OPEN local_table_cursor
FETCH NEXT FROM local_table_cursor
INTO @schema, @table, @objectId;

WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE local_change_cursor CURSOR FOR
    SELECT ROW_NUMBER() OVER (ORDER BY c.column_id) AS row_id
        , c.name column_name
        , t.Name data_type
        , c.max_length
        , c.column_id
        , c.is_nullable
    FROM 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 AND ic.column_id = c.column_id
    LEFT OUTER JOIN sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
    WHERE c.object_id = @objectId
    ORDER BY c.column_id

    OPEN local_change_cursor
    FETCH NEXT FROM local_change_cursor
    INTO @row_id, @column_name, @data_type, @max_length, @column_id, @is_nullable

    WHILE @@FETCH_STATUS = 0
    BEGIN
        IF (@max_length = -1) SET @max_length = 4000;
        set @null=' NOT NULL'
        if (@is_nullable = 1) Set @null=' NULL'
        if (@Data_type='nvarchar') set @max_length=cast(@max_length/2 as bigint)
        IF (@data_type LIKE '%char%')
        BEGIN TRY
            SET @sql = 'ALTER TABLE ' + @schema + '.' + @table + ' ALTER COLUMN [' + rtrim(@column_name) + '] ' + @data_type + '(' + CAST(@max_length AS nvarchar(100)) +  ') COLLATE ' + @collate + @null
            PRINT @sql
            EXEC sp_executesql @sql
        END TRY
        BEGIN CATCH
          PRINT 'ERROR: Some index or contraint rely on the column ' + @column_name + '. No conversion possible.'
          PRINT @sql
        END CATCH

        FETCH NEXT FROM local_change_cursor
        INTO @row_id, @column_name, @data_type, @max_length, @column_id, @is_Nullable

    END

    CLOSE local_change_cursor
    DEALLOCATE local_change_cursor

    FETCH NEXT FROM local_table_cursor
    INTO @schema,@table,@objectId

END

CLOSE local_table_cursor
DEALLOCATE local_table_cursor

GO

Answer 5

To do this I have an easy solution that works for me.

1. Create a new database with the new collation.
2. Export the data of the original data base in script mode.
3. Import the content to the new database using the script (rename the USE sentence to the new database).

However, you need to exercise caution if your database has triggers, procedures or similar - more that just data and tables.

Answer 6

Fixed length problem nvarchar (include max), included text and added NULL/NOT NULL.

USE [put your database name here];

begin tran

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 @max_length_str nvarchar(100);
DECLARE @is_nullable bit;
DECLARE @row_id int;
DECLARE @sql nvarchar(max);
DECLARE @sql_column nvarchar(max);

SET @collate = 'Latin1_General_CI_AS';

DECLARE local_table_cursor CURSOR FOR

SELECT [name]
FROM sysobjects
WHERE OBJECTPROPERTY(id, N'IsUserTable') = 1
ORDER BY [name]

OPEN local_table_cursor
FETCH NEXT FROM local_table_cursor
INTO @table

WHILE @@FETCH_STATUS = 0
BEGIN

    DECLARE local_change_cursor CURSOR FOR

    SELECT ROW_NUMBER() OVER (ORDER BY c.column_id) AS row_id
        , c.name column_name
        , t.Name data_type
        , col.CHARACTER_MAXIMUM_LENGTH
        , c.column_id
        , c.is_nullable
    FROM sys.columns c
    JOIN sys.types t ON c.system_type_id = t.system_type_id
    JOIN INFORMATION_SCHEMA.COLUMNS col on col.COLUMN_NAME = c.name and c.object_id = OBJECT_ID(col.TABLE_NAME)
    LEFT OUTER JOIN sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
    LEFT OUTER JOIN sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
    WHERE c.object_id = OBJECT_ID(@table) AND (t.Name LIKE '%char%' OR t.Name LIKE '%text%') 
    AND c.collation_name <> @collate
    ORDER BY c.column_id

    OPEN local_change_cursor
    FETCH NEXT FROM local_change_cursor
    INTO @row_id, @column_name, @data_type, @max_length, @column_id, @is_nullable

    WHILE @@FETCH_STATUS = 0
    BEGIN

        set @max_length_str = @max_length
        IF (@max_length = -1) SET @max_length_str = 'max'
        IF (@max_length > 4000) SET @max_length_str = '4000'

        BEGIN TRY
            SET @sql =
            CASE 
                WHEN @data_type like '%text%' 
                THEN 'ALTER TABLE ' + @table + ' ALTER COLUMN [' + @column_name + '] ' + @data_type + ' COLLATE ' + @collate + ' ' + CASE WHEN @is_nullable = 0 THEN 'NOT NULL' ELSE 'NULL' END
                ELSE 'ALTER TABLE ' + @table + ' ALTER COLUMN [' + @column_name + '] ' + @data_type + '(' + @max_length_str + ') COLLATE ' + @collate + ' ' + CASE WHEN @is_nullable = 0 THEN 'NOT NULL' ELSE 'NULL' END
            END
            --PRINT @sql
            EXEC sp_executesql @sql
        END TRY
        BEGIN CATCH
          PRINT 'ERROR (' + @table + '): Some index or constraint rely on the column ' + @column_name + '. No conversion possible.'
          --PRINT @sql
        END CATCH

        FETCH NEXT FROM local_change_cursor
        INTO @row_id, @column_name, @data_type, @max_length, @column_id, @is_nullable

    END

    CLOSE local_change_cursor
    DEALLOCATE local_change_cursor

    FETCH NEXT FROM local_table_cursor
    INTO @table

END

CLOSE local_table_cursor
DEALLOCATE local_table_cursor

commit tran

GO

Notice : in case when you just need to change some specific collation use condition like this :

WHERE c.object_id = OBJECT_ID(@table) AND (t.Name LIKE '%char%' OR t.Name LIKE '%text%') 
    AND c.collation_name = 'collation to change'

e.g. NOT the : AND c.collation_name <> @collate

In my case, I had correct / specified collation of some columns and didn't want to change them.

Answer 7

Sorry late to the party, but here is mine - cater for table with a schema and funny column and table names. Yes I had some of them.

SELECT 
    'ALTER TABLE [' +  TABLE_SCHEMA + '].[' + TABLE_NAME  
    + '] ALTER COLUMN [' + COLUMN_NAME + '] ' + DATA_TYPE 
    + '(' + CAST(CHARACTER_MAXIMUM_LENGTH AS nvarchar(100)) 
    + ') COLLATE ' + 'Latin1_General_CI_AS' 
    + CASE WHEN IS_NULLABLE = 'YES' THEN ' NULL' ELSE ' NOT NULL' END 
FROM 
    INFORMATION_SCHEMA.COLUMNS 
WHERE 
    DATA_TYPE like '%char'

Answer 8

Following script will work with table schema along with latest Types like (MAX), IMAGE, and etc. change your collation type according to your need on this line (SET @collate = 'DATABASE_DEFAULT';)

SQL SCRIPT HERE:

BEGIN
DECLARE @collate nvarchar(100);
declare @schema nvarchar(255);
DECLARE @table nvarchar(255);
DECLARE @column_name nvarchar(255);
DECLARE @column_id int;
DECLARE @data_type nvarchar(255);
DECLARE @max_length varchar(100);
DECLARE @row_id int;
DECLARE @sql nvarchar(max);
DECLARE @sql_column nvarchar(max);

SET @collate = 'DATABASE_DEFAULT';

DECLARE tbl_cursor CURSOR FOR SELECT (s.[name])schemaName, (o.[name])[tableName]
FROM sysobjects sy 
INNER JOIN sys.objects  o on o.name = sy.name
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE OBJECTPROPERTY(sy.id, N'IsUserTable') = 1

OPEN tbl_cursor FETCH NEXT FROM tbl_cursor INTO @schema,@table

WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE tbl_cursor_changed CURSOR FOR
        SELECT ROW_NUMBER() OVER (ORDER BY c.column_id) AS row_id
            , c.name column_name
            , t.Name data_type
            , c.max_length
            , c.column_id
        FROM 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 AND ic.column_id = c.column_id
        LEFT OUTER JOIN sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
    WHERE c.object_id like OBJECT_ID(@schema+'.'+@table)
    ORDER BY c.column_id


    OPEN tbl_cursor_changed 
     FETCH NEXT FROM tbl_cursor_changed
    INTO @row_id, @column_name, @data_type, @max_length, @column_id



    WHILE @@FETCH_STATUS = 0
    BEGIN
    IF (@max_length = -1) SET @max_length = 'MAX';
        IF (@data_type LIKE '%char%')
        BEGIN TRY
            SET @sql = 'ALTER TABLE ' +@schema+'.'+ @table + ' ALTER COLUMN ' + @column_name + ' ' + @data_type + '(' + CAST(@max_length AS nvarchar(100)) + ') COLLATE ' + @collate
            print @sql
            EXEC sp_executesql @sql
        END TRY
        BEGIN CATCH
          PRINT 'ERROR:'
          PRINT @sql
        END CATCH

        FETCH NEXT FROM tbl_cursor_changed
        INTO @row_id, @column_name, @data_type, @max_length, @column_id

    END

    CLOSE tbl_cursor_changed
    DEALLOCATE tbl_cursor_changed

    FETCH NEXT FROM tbl_cursor
    INTO @schema, @table

END

CLOSE tbl_cursor
DEALLOCATE tbl_cursor

PRINT 'Collation For All Tables Done!'
END

Answer 9

I've used the following query to generate collation update queries on each field of a table:

SELECT
    [query] = 
        'ALTER TABLE '+ c.TABLE_SCHEMA +
        '.[' + c.TABLE_NAME + 
        '] ALTER COLUMN [' + c.COLUMN_NAME + 
        '] nvarchar(' + IIF(c.CHARACTER_MAXIMUM_LENGTH = -1, 'MAX', CAST(c.CHARACTER_MAXIMUM_LENGTH AS NVARCHAR(50))) + 
        ') COLLATE Persian_100_CI_AI_SC_UTF8'
FROM INFORMATION_SCHEMA.COLUMNS AS c
LEFT JOIN sys.computed_columns  AS cc  ON cc.[name] = c.COLUMN_NAME
WHERE c.TABLE_CATALOG = 'Your_Table_Name' AND DATA_TYPE = 'nvarchar' AND cc.[object_id] IS NULL
    AND c.TABLE_NAME NOT IN ('sysdiagrams')
ORDER BY c.TABLE_NAME

It's not work on Computed Columns

Answer 10

I always prefer pure SQL so :

SELECT 'ALTER TABLE [' + l.schema_n + '].[' 
       + l.table_name + '] ALTER COLUMN [' 
       + l.column_name + '] ' + l.data_type + '(' 
       + Cast(l.new_max_length AS NVARCHAR(100)) 
       + ') COLLATE ' + l.dest_collation_name + ';', 
       l.schema_n, 
       l.table_name, 
       l.column_name, 
       l.data_type, 
       l.max_length, 
       l.collation_name 
FROM   (SELECT Row_number() 
                 OVER ( 
                   ORDER BY c.column_id) AS row_id, 
               Schema_name(o.schema_id)  schema_n, 
               ta.NAME                   table_name, 
               c.NAME                    column_name, 
               t.NAME                    data_type, 
               c.max_length, 
               CASE 
                 WHEN c.max_length = -1 
                       OR ( c.max_length > 4000 ) THEN 4000 
                 ELSE c.max_length 
               END                       new_max_length, 
               c.column_id, 
               c.collation_name, 
               'French_CI_AS'            dest_collation_name 
        FROM   sys.columns c 
               INNER JOIN sys.tables ta 
                       ON c.object_id = ta.object_id 
               INNER JOIN sys.objects o 
                       ON c.object_id = o.object_id 
               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 
                               AND ic.column_id = c.column_id 
               LEFT OUTER JOIN sys.indexes i 
                            ON ic.object_id = i.object_id 
                               AND ic.index_id = i.index_id 
        WHERE  1 = 1 
               AND c.collation_name = 'SQL_Latin1_General_CP1_CI_AS' 
       --'French_CI_AS'-- ALTER DONE YET OLD VALUE :'SQL_Latin1_General_CP1_CI_AS' 
       ) l 
ORDER  BY l.column_id;

Answer 11

Using the cursor based variations above as a starting point, the script below will just output a set of UPDATE statements to set to DATABASE_DEFAULT, it won't actually do the UPDATES.

It supports schema, the full set of char and text types and retains the existing NULL / NOT NULL.

I plan to use the output to find to statements that fail in a lower environment and then manually adapt the resulting script to drop and recreate the constraints as needed.

DECLARE @collate nvarchar(100);
DECLARE @schema nvarchar(255);
DECLARE @table nvarchar(255);
DECLARE @column_name nvarchar(255);
DECLARE @column_id int;
DECLARE @data_type nvarchar(255);
DECLARE @max_length int;
DECLARE @max_length_str nvarchar(100);
DECLARE @is_nullable bit;
DECLARE @row_id int;
DECLARE @sql nvarchar(max);
DECLARE @sql_column nvarchar(max);

SET @collate = 'DATABASE_DEFAULT';

DECLARE local_table_cursor CURSOR FOR

SELECT (s.[name])schemaName, (o.[name])[tableName]
FROM sysobjects sy 
INNER JOIN sys.objects  o on o.name = sy.name
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE OBJECTPROPERTY(sy.id, N'IsUserTable') = 1
ORDER BY s.[name], o.[name]

OPEN local_table_cursor FETCH NEXT FROM local_table_cursor INTO @schema,@table

WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE local_change_cursor CURSOR FOR

    SELECT ROW_NUMBER() OVER (ORDER BY c.column_id) AS row_id
        , c.name column_name
        , t.Name data_type
        , col.CHARACTER_MAXIMUM_LENGTH
        , c.column_id
        , c.is_nullable
    FROM sys.columns c
    JOIN sys.types t ON c.system_type_id = t.system_type_id
    JOIN INFORMATION_SCHEMA.COLUMNS col on col.COLUMN_NAME = c.name and c.object_id = OBJECT_ID(col.TABLE_NAME)
    LEFT OUTER JOIN sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
    LEFT OUTER JOIN sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
    WHERE c.object_id = OBJECT_ID(@schema+'.'+@table) AND (t.Name LIKE '%char%' OR t.Name LIKE '%text%') 
    ORDER BY c.column_id

    OPEN local_change_cursor
    FETCH NEXT FROM local_change_cursor
    INTO @row_id, @column_name, @data_type, @max_length, @column_id, @is_nullable

    WHILE @@FETCH_STATUS = 0
    BEGIN

        SET @max_length_str = @max_length
        IF (@max_length = -1) SET @max_length_str = 'max'
        IF (@max_length > 4000) SET @max_length_str = '4000'

        SET @sql =
        CASE 
            WHEN @data_type like '%text%' 
            THEN 'ALTER TABLE [' + @schema+ '].['+ @table + '] ALTER COLUMN [' + @column_name + '] ' + @data_type + ' COLLATE ' + @collate + ' ' + CASE WHEN @is_nullable = 0 THEN 'NOT NULL' ELSE 'NULL' END
            ELSE 'ALTER TABLE [' + @schema+ '].['+ @table + '] ALTER COLUMN [' + @column_name + '] ' + @data_type + '(' + @max_length_str + ') COLLATE ' + @collate + ' ' + CASE WHEN @is_nullable = 0 THEN 'NOT NULL' ELSE 'NULL' END
        END
        PRINT @sql

        FETCH NEXT FROM local_change_cursor
        INTO @row_id, @column_name, @data_type, @max_length, @column_id, @is_nullable

    END

    CLOSE local_change_cursor
    DEALLOCATE local_change_cursor

    FETCH NEXT FROM local_table_cursor
    INTO @schema, @table
END

CLOSE local_table_cursor
DEALLOCATE local_table_cursor
GO

Answer 12

The easiest way to change the collation of a database including all fields goes over merge replication:

• Take a server with the target collation (server collation prop)
• Create a merge publication on the old server
• Add all products of the source database to the publication
• Run the snapsoht agent and wait it is completed
• Add a push subscription to your publication targeting the server with the nice collation
• Initialize Subscription
• Check in replication monitor and wait until agent ready
• Delete Subsciption
• Delete Publication

The following sql scipt creates a merge publication for your user tables The other steps I do in Managemet Studio, as also I create the script objects like stored procedures, views etc in a subsequents step with a separate scirpt.

ALTER PROCEDURE [dbo].[CreateMergePublication]
    @PublicationName nvarchar(max) = N'Pubi'
AS BEGIN
SET NOCOUNT ON
    BEGIN TRY


        -- *** BEGIN BLL ***


        declare @DBName nvarchar(max) 
        select top 1 @DBName = TABLE_CATALOG from INFORMATION_SCHEMA.TABLES
        exec sp_replicationdboption @dbname = @DBName, @optname = N'merge publish', @value = N'true'

        -- Mergeveröffentlichung wird hinzugefügt
        declare @desc nvarchar(max) = N'Mergeveröffentlichung der ' + @dbname + '-Datenbank von Verleger ' + @@SERVERNAME
        exec sp_addmergepublication 
            @publication = @PublicationName, 
            @description = @desc , 
            @sync_mode = N'native', 
            @retention = 14, 
            @allow_push = N'true', 
            @allow_pull = N'true', 
            @allow_anonymous = N'true', 
            @enabled_for_internet = N'false', 
            @snapshot_in_defaultfolder = N'true', 
            @compress_snapshot = N'false', 
            @ftp_port = 21, 
            @ftp_subdirectory = N'ftp', 
            @ftp_login = N'anonymous', 
            @allow_subscription_copy = N'false', 
            @add_to_active_directory = N'false', 
            @dynamic_filters = N'false', 
            @conflict_retention = 14, 
            @keep_partition_changes = N'false', 
            @allow_synctoalternate = N'false', 
            @max_concurrent_merge = 0, 
            @max_concurrent_dynamic_snapshots = 0, 
            @use_partition_groups = null, 
            @publication_compatibility_level = N'100RTM', 
            @replicate_ddl = 1, 
            @allow_subscriber_initiated_snapshot = N'false', 
            @allow_web_synchronization = N'false', 
            @allow_partition_realignment = N'true', 
            @retention_period_unit = N'days', 
            @conflict_logging = N'both', 
            @automatic_reinitialization_policy = 0


        exec sp_addpublication_snapshot 
            @publication = @PublicationName, 
            @frequency_type = 4, 
            @frequency_interval = 14, 
            @frequency_relative_interval = 1, 
            @frequency_recurrence_factor = 0, 
            @frequency_subday = 1, 
            @frequency_subday_interval = 5, 
            @active_start_time_of_day = 500, 
            @active_end_time_of_day = 235959, 
            @active_start_date = 0, 
            @active_end_date = 0, 
            @job_login = null, 
            @job_password = null, 
            @publisher_security_mode = 1

        declare @schema nvarchar(max), @table nvarchar(max), @uniquename nvarchar(max)
        declare cr cursor for
            select TABLE_SCHEMA, TABLE_NAME from INFORMATION_SCHEMA.TABLES 
            where TABLE_TYPE = 'BASE TABLE' and TABLE_NAME not like 'sys%' and TABLE_NAME not like 'ms%' and TABLE_NAME not like 'dtprop%'
            order by TABLE_NAME
        open cr
        WHILE 1=1 BEGIN
            FETCH cr INTO @schema, @table
            IF @@FETCH_STATUS <> 0 BREAK
            set @uniquename = @schema + @table

            print @schema + '.' + @table + ' (' + @uniquename + ')'
            exec sp_addmergearticle 
                @publication = @PublicationName, 
                @article = @uniquename, 
                @source_owner = @schema, 
                @source_object = @table, 
                @type = N'table', 
                @description = N'', 
                @creation_script = null, 
                @pre_creation_cmd = N'none', 
                @schema_option = 0x000000010C034FD1, 
                @identityrangemanagementoption = N'manual', 
                @destination_owner = @schema, 
                @force_reinit_subscription = 1, 
                @column_tracking = N'false', 
                @subset_filterclause = N'', 
                @vertical_partition = N'false', 
                @verify_resolver_signature = 1, 
                @allow_interactive_resolver = N'false', 
                @fast_multicol_updateproc = N'true', 
                @check_permissions = 0, 
                @subscriber_upload_options = 0, 
                @delete_tracking = N'true', 
                @compensate_for_errors = N'false', 
                @stream_blob_columns = N'false', 
                @partition_options = 0

        END 

        close cr
        deallocate cr





        -- *** END BLL ***

    END TRY
    BEGIN CATCH
        IF CURSOR_STATUS('global','cr') >= 0
        BEGIN 
            close cr
            deallocate cr
        END

        DECLARE @ErrMsg nvarchar(4000), @ErrSeverity INT, @ErrorState INT;
        SELECT @ErrMsg = ERROR_MESSAGE(),@ErrSeverity = ERROR_SEVERITY(),@ErrorState = ERROR_STATE()
        RAISERROR(@ErrMsg, @ErrSeverity, @ErrorState)


    END CATCH;

END

Answer 13

This answer is maybe a bit late but you could generate the statements as well. This will be much faster than using CURSOR.

select 
'ALTER TABLE '+ tb.TABLE_NAME + ' ALTER COLUMN '+ cl.COLUMN_NAME + ' ' + DATA_TYPE +'('+ cast(CHARACTER_MAXIMUM_LENGTH as nchar(3)) + ') ' + 'COLLATE Latin1_General_CI_AS '
FROM [DATABASE].INFORMATION_SCHEMA.columns cl  
left join [DATABASE].tables tb on tb.TABLE_NAME = cl.TABLE_NAME and tb.Table_Schema=cl.table_schema 
left join  [DATABASE].INFORMATION_SCHEMA.KEY_COLUMN_USAGE kc on kc.table_name = tb.table_name and kc.TABLE_SCHEMA = cl.TABLE_SCHEMA  and kc.column_name=cl.COLUMN_NAME
WHERE DATA_TYPE in ('nvarchar','nchar')

Hope this can helps somebody.