首先,更改数据库排序规则不会影响表中现有列,但会影响新创建的表和列。这是在数据库中创建新对象时使用的默认排序规则。
所以,你需要找到所有现有的列并更新当前的排序规则。
你可以使用 SQL 游标查询所有表和列,并为特定类型或列名更新排序规则。
例如:
1)你可以更改未来对象的排序规则
USE master;
GO
ALTER DATABASE databasename COLLATE French_CI_AI ;
GO
2) 手动更改特定列的列排序规则
ALTER TABLE tablenameX ALTER COLUMN LastName varchar(100) COLLATE French_CI_AI NULL
ALTER TABLE tablenameY ALTER COLUMN FirstName varchar(100) COLLATE French_CI_AI NULL
3)使用游标生成SQL脚本
您需要决定要更改排序规则的列。可以按类型、名称或特定表进行更改。
您可以从示例中查看列游标,了解您拥有哪种字符类型,并将其替换。
SELECT distinct DATA_TYPE from information_schema.columns
您可以参考此帖子:
http://www.codeproject.com/Articles/302405/The-Easy-way-of-changing-Collation-of-all-Database
declare @CollationName as nvarchar = 'Latin1_General_CI_AS'
declare @tablename as nvarchar(100) = ''
declare @sqltext as nvarchar(100) = ''
declare @columnname as nvarchar(200) = ''
declare @DataType as nvarchar(100) = ''
declare @CharacterMaxLen as int = 0
declare @IsNullable as bit = 0
DECLARE MyTableCursor Cursor
FOR
SELECT * from information_schema.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
OPEN MyTableCursor
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE MyColumnCursor Cursor
FOR
SELECT COLUMN_NAME,DATA_TYPE, CHARACTER_MAXIMUM_LENGTH,
IS_NULLABLE from information_schema.columns
WHERE table_name = @TableName AND (Data_Type LIKE '%char%'
OR Data_Type LIKE '%text%') AND COLLATION_NAME <> @CollationName
ORDER BY ordinal_position
Open MyColumnCursor
FETCH NEXT FROM MyColumnCursor INTO @ColumnName, @DataType,
@CharacterMaxLen, @IsNullable
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQLText = 'ALTER TABLE ' + @TableName + ' ALTER COLUMN [' + @ColumnName + '] ' +
@DataType + '(' + CASE WHEN @CharacterMaxLen = -1 THEN 'MAX' ELSE @CharacterMaxLen END +
') COLLATE ' + @CollationName + ' ' +
CASE WHEN @IsNullable = 'NO' THEN 'NOT NULL' ELSE 'NULL' END
PRINT @SQLText
FETCH NEXT FROM MyColumnCursor INTO @ColumnName, @DataType,
@CharacterMaxLen, @IsNullable
END
CLOSE MyColumnCursor
DEALLOCATE MyColumnCursor
FETCH NEXT FROM MyTableCursor INTO @TableName
END
CLOSE MyTableCursor
DEALLOCATE MyTableCursor