修改SQL Server中所有表的所有列。

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.

目标数据库有collation Latin1_General_CI_AS，源数据库有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.

我确实使用SQL Server Management Studio将源数据库的排序规则修改为Latin1_General_CI_AS。但是里面的桌子和柱子仍然保留着旧的校勘。

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

在我开始编写一个存储过程之前，读取所有的表和varchar类型的所有列，并在表和列游标循环中更改它们……

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

有没有人知道一种更简单的方法，或者是在一个过程中运行在所有表中的脚本的唯一方法?

6 个解决方案

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

因为我没有找到合适的方法，所以我写了一个脚本来完成它，我在这里分享给那些需要它的人。脚本运行在所有用户表中并收集列。如果列类型是任何char类型，则尝试将其转换为给定的排序规则。

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

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

我再一次对这个答案不满意。我的任务是升级JIRA 6.4。x到JIRA软件7。我和x谈到了数据库和列排序的问题。

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.

在SQL Server中，如果不删除主键、外键甚至索引等约束，上面提供的作为答案的脚本就根本不起作用。但是它会改变那些没有这些属性的。这确实有问题，因为我不想手动删除所有约束并创建它们。这个操作可能最终会出现错误。另一方面，创建一个自动化更改的脚本可能需要花费很长时间。

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

因此，我找到了一种简单地使用SQL管理Studio进行迁移的方法。过程是这样的:

• Rename the database by something else. By example, mine's was "Jira", so I renamed it "JiraTemp".
• 用其他东西重命名数据库。举个例子，我的名字是“Jira”，所以我把它改名为“JiraTemp”。
• Create a new database named "Jira" and make sure to set the right collation. Simply select the page "Options" and change the collation.
• 创建一个名为“Jira”的新数据库，并确保设置正确的排序。只需选择“选项”页面并更改排序。
• 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"
  • 选择“Save to new query window”，选择“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.
  • 如果你愿意的话，把其他的一切都保留原样或者个性化。
• 创建之后，回到“JiraTemp”，右键单击它，“Tasks ->生成脚本…”。选择“脚本整个数据库和所有数据库对象”。选择“Save to new query window”，然后选择“Advanced”，将“Script for Server Version”的值改为“Script Object-Level Permissions”、“Script Owner”和“Script Full-Text Indexes”。
• Once generated, delete the "CREATE DATABASE" section. Replace "JiraTemp" by "Jira".
• 生成后，删除“创建数据库”部分。由“Jira”取代“JiraTemp”。
• Run the script. The entire database structure and permissions of the database is now replicated to "Jira".
• 运行脚本。数据库的整个数据库结构和权限现在被复制到“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"
• 在复制数据之前，我们需要禁用所有约束。在数据库“Jira”中执行以下命令:EXEC sp_msforeachtable“ALTER TABLE”?NOCHECK约束”
• 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.
  • 选择SQL Server的OLE DB提供程序作为数据源和目标。
  • Source database is "JiraTemp"
  • 源数据库是“JiraTemp”
  • Destination database is "Jira"
  • 目标数据库是“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
  • 单击OK，然后单击Finish
• 现在需要传输数据。为此，只需右键单击“JiraTemp”，然后选择“Tasks ->导出数据…”选择SQL Server的OLE DB提供程序作为数据源和目标。源数据库是“JiraTemp”目标数据库是“Jira”，服务器名称在技术上与源和目标相同(除非您在另一个服务器上创建了数据库)。选择“从一个或另一个表或视图中复制数据”选择除视图之外的所有表。然后，当仍然突出显示时，单击“编辑映射”。勾选“启用身份插入”点击OK，下一步，然后完成
• 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"
• 数据传输需要一段时间。完成后，执行以下命令重新启用所有约束:exec sp_msforeachtable @command1="print " ?’”,@command2 = " ALTER TABLE ?带有校验限制"

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

完成之后，我重新启动了JIRA，我的数据库排序已经就绪。希望它能帮助很多人!

#3

Fixed length problem nvarchar and added NULL/NOT NULL

固定长度问题nvarchar和添加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

#4

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

为了做到这一点，我得到了一个简单的解决方案。

1. Create a new database with the new collation.
2. 使用新的排序规则创建一个新的数据库。
3. Export the data of the original data base in script mode.
4. 以脚本模式导出原始数据库的数据。
5. Import the content to the new database using the script (rename the USE sentence to the new database).
6. 使用脚本将内容导入新数据库(将使用语句重命名为新数据库)。

However, you need to get caution if you database has got triggers, procedures or similar stuffs more that data and tables.

但是，如果您的数据库有触发器、过程或类似的东西，那么您需要格外小心。

#5

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

固定长度问题nvarchar(包括max)，包含文本并添加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

不是:和c。collation_name < > @collate

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

在我的例子中，我对一些列进行了正确的/指定的排序，并且不想更改它们。

#6

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'