Change collations of all columns of all tables in SQL Server

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 constrains 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 constrains and create them back. That operation could probably ends 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 by something else. By example, mine's 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" for 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" by "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 constrains. 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 constrains: exec sp_msforeachtable @command1="print '?'", @command2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

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


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

Tags:

Sql Server

Collate

Recent Posts