更改SQL Server中所有表的所有列的排序规则 [英] Change collations of all columns of all tables in SQL Server

问题描述

我导入了一个包含一些数据的数据库，以便与另一个数据库进行比较。

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

目标数据库具有排序规则 Latin1_General_CI_AS 并且源数据库具有 SQL_Latin1_General_CP1_CI_AS 。

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

我确实将源数据库的排序规则通常更改为 Latin1_General_CI_AS 使用SQL Server Management Studio。但是里面的表和列仍然保留着旧的排序规则。

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.

在我知道开始编写一个存储过程之前，该过程将读取 varchar 类型的所有表和所有列内，并在表和列光标循环中对其进行更改...

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?

推荐答案

由于找不到合适的方法，我编写了脚本来完成此任务，并在此与需要它的人分享。该脚本贯穿所有用户表并收集列。如果列类型是任何char类型，则它将尝试将其转换为给定的排序规则。

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.

如果有人对此还有更好的解决方案，请发布！

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

这篇关于更改SQL Server中所有表的所有列的排序规则的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！