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?

Solution 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

Solution 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 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!

Solution 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

Solution 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

Solution 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.

View more solutions

Comments

[Damien_The_Unbeliever]

possible duplicate of how to change the collate to all the columns of the database?

[YvesR]

Thanks for the link. Seems the answer there is similiar to the idea I had with the script I did.

[NullUserException]

codeproject.com/Articles/302405/…

[Kodak]

join with index_columns and indexes are not used and actually cause column duplication; one can also use a filtering to find only columns necessary to convert (c.collation_name <> @collate)

[pkExec]

Followed this procedure to the letter, got errors on the copy data step: "The column XXXX cannot be processed because more than one code page (X and Y) are specified for it. Luckily it was only on one table, so I excluded that table from the copy step and later copied it manually. So it's not a foolproof solution.

[David]

Since I am not aware of any details about which environment this has been done, neither which version of SQL Server was used, I cannot really say what's the exact cause. A quick search on Google seems to say it is indeed related to data transfer. Here's what I've found for others having the problem: dba.stackexchange.com/questions/22010/… or social.msdn.microsoft.com/Forums/sqlserver/en-US/…

[trebor]

Specific to those here for JIRA upgrades to the 7.x series, I found that it was easier to do an XML backup, drop the database, recreate with the correct collation, and then import from XML. See confluence.atlassian.com/adminjiraserver072/…

[Pecan]

I ran in the same issues as pkExec (code page differences). As a worarround I used following script: EXEC sp_msforeachtable "PRINT 'INSERT INTO <new_db_name>.? SELECT * FROM ?;'" and then executed the inserts. I hope this could help someone save some time...

[David]

Out of curiosity, what version of SQL Server are you using?

[Tod]

If you use schemas you should chanfge the Select [name] from sysobjects to: SELECT sys.schemas.name + '.' + sys.objects.name AS Name FROM sys.objects INNER JOIN sys.schemas ON sys.objects.schema_id = sys.schemas.schema_id WHERE type_desc = 'USER_TABLE'

[Joe Murray]

Gaps when trying to use this to convert a Blackbaud Raiser's Edge database included Windows NT user or group not found, related Cannot add the principal because it does not exist or you do not have permission, CREATE or ALTER ASSEMBLY for assembly ... with the SAFE or EXTERNAL_ACCESS option failed because the 'clr strict security' option..., Assembly...not found, CREATE SCHEMA failed due to previous errors, etc.

[Joe Murray]

Although there were several hundred additional errors, the tables were created. And data migration was possible.

[vast]

This code works for me only indexed columns has to recreate. Jus want to add the line below if you update yor code with this line would be perfect. SET @sql = 'ALTER TABLE [' + @table + '] ALTER COLUMN [' + @column_name + '] ' + @data_type + '(' + CAST(@max_length AS nvarchar(100)) + ') COLLATE ' + @collate

[QMaster]

It seems to not changing key fields!

[Uwe Keim]

Can you tell, which changes you've made? Anyway, I've used your script successfully. So thanks for it.

[Darren]

While it is good that this one handles schema, beware that it does not handle NULL vs NOT NULL as some of others here do.

[Darren]

Hang on, looks like someone has handled the constraints too: stackoverflow.com/a/44587493/329367

[mishsx]

Is there a SQL script that explains your flow you illustrated ?

[cuttajoe]

See my edit to create the merge publication. The other steps I normally do manually in Management Studio.

[FAB]

Need to add a case for the CHARACTER_MAXIMUM_LENGTH to handle MAX, but otherwise this does the job.

[SiBrit]

Why have Microsoft have never learnt how to deal with errors properly? I get a number of failure messages on Validating, such as: Failure inserting into the read-only column "UniqueId". Column metadata validation failed. One or more component failed validation. There were errors during task validation. Unfortunately there is no specific information about which tables/columns are at fault, so I'm left with turning them on and off to find out. This is with SQL Server 2019.