如何将排序规则更改为数据库的所有列? [英] how to change the collate to all the columns of the database?
问题描述
我想更改数据库所有表的所有列的排序规则。在堆栈溢出中,我找到了以下脚本:( post )
I would like to change the collation of all the columns of all the tables of my database. In stack overflow, I have found this script: (post)
SELECT 'ALTER TABLE [' + SYSOBJECTS.Name + '] ALTER COLUMN [' + SYSCOLUMNS.Name + '] ' +
SYSTYPES.name +
CASE systypes.NAME
WHEN 'text' THEN ' '
ELSE
'(' + RTRIM(CASE SYSCOLUMNS.length
WHEN -1 THEN 'MAX'
ELSE CONVERT(CHAR,SYSCOLUMNS.length)
END) + ') '
END
+ ' ' + ' COLLATE Latin1_General__CI_AI ' + CASE ISNULLABLE WHEN 0 THEN 'NOT NULL' ELSE 'NULL' END
FROM SYSCOLUMNS , SYSOBJECTS , SYSTYPES
WHERE SYSCOLUMNS.ID = SYSOBJECTS.ID
AND SYSOBJECTS.TYPE = 'U'
AND SYSTYPES.Xtype = SYSCOLUMNS.xtype
AND SYSCOLUMNS.COLLATION IS NOT NULL
AND NOT ( sysobjects.NAME LIKE 'sys%' )
AND NOT ( SYSTYPES.name LIKE 'sys%' )
GO
但是,当我看到列的排序规则时,我看到该排序规则是旧的排序规则。
However, when I see the collation of the columns I see that the collation is the old collation.
实际的排序规则是AS,因此我可以将 ANIMAL和ÁNIMAL。执行脚本时,没有任何错误。我认为会出错,因为新的归类是AI。因此,这使我认为脚本无能为力。
The actual collation is AS, so I can have "ANIMAL" and "ÁNIMAL". When I execute the script, I don't get any error. I think that I would get an error because the new collation is AI. So this makes me think that the script does nothing.
如何更改数据库中所有表的所有列的排序规则?
How can I change the collation of all the columns of all my tables in the database?
谢谢。
推荐答案
尝试一下-
查询:
DECLARE @collate SYSNAME
SELECT @collate = 'Cyrillic_General_CS_AS'
SELECT
'[' + SCHEMA_NAME(o.[schema_id]) + '].[' + o.name + '] -> ' + c.name
, 'ALTER TABLE [' + SCHEMA_NAME(o.[schema_id]) + '].[' + o.name + ']
ALTER COLUMN [' + c.name + '] ' +
UPPER(t.name) +
CASE WHEN t.name NOT IN ('ntext', 'text')
THEN '(' +
CASE
WHEN t.name IN ('nchar', 'nvarchar') AND c.max_length != -1
THEN CAST(c.max_length / 2 AS VARCHAR(10))
WHEN t.name IN ('char', 'varchar') AND c.max_length != -1
THEN CAST(c.max_length AS VARCHAR(10))
WHEN t.name IN ('nchar', 'nvarchar', 'char', 'varchar') AND c.max_length = -1
THEN 'MAX'
ELSE CAST(c.max_length AS VARCHAR(10))
END + ')'
ELSE ''
END + ' COLLATE ' + @collate +
CASE WHEN c.is_nullable = 1
THEN ' NULL'
ELSE ' NOT NULL'
END
FROM sys.columns c
JOIN sys.objects o ON c.[object_id] = o.[object_id]
JOIN sys.types t ON c.system_type_id = t.system_type_id AND c.user_type_id = t.user_type_id
WHERE t.name IN ('char', 'varchar', 'text', 'nvarchar', 'ntext', 'nchar')
AND c.collation_name != @collate
AND o.[type] = 'U'
输出:
-------------------------------------------------- ------------------------------------------------------------------------------------------------------------------
[dbo].[CategoryType] -> CategoryTypeCD ALTER TABLE [dbo].[CategoryType] ALTER COLUMN [CategoryTypeCD] VARCHAR(20) COLLATE Cyrillic_General_CI_AS NOT NULL
[dbo].[Employee] -> TabelNumber ALTER TABLE [dbo].[Employee] ALTER COLUMN [TabelNumber] VARCHAR(12) COLLATE Cyrillic_General_CI_AS NULL
[dbo].[Supplement] -> WorkFactorCD ALTER TABLE [dbo].[Supplement] ALTER COLUMN [WorkFactorCD] VARCHAR(50) COLLATE Cyrillic_General_CI_AS NOT NULL
[dbo].[Surcharge] -> WorkFactorCD ALTER TABLE [dbo].[Surcharge] ALTER COLUMN [WorkFactorCD] VARCHAR(50) COLLATE Cyrillic_General_CI_AS NOT NULL
[dbo].[Surcharge] -> Condition ALTER TABLE [dbo].[Surcharge] ALTER COLUMN [Condition] NVARCHAR(MAX) COLLATE Cyrillic_General_CI_AS NULL
[dbo].[WorkFactor] -> WorkFactorCD ALTER TABLE [dbo].[WorkFactor] ALTER COLUMN [WorkFactorCD] VARCHAR(50) COLLATE Cyrillic_General_CI_AS NOT NULL
[dbo].[WorkFactor] -> Name ALTER TABLE [dbo].[WorkFactor] ALTER COLUMN [Name] NVARCHAR(200) COLLATE Cyrillic_General_CI_AS NOT NULL
[dbo].[WorkOut] -> WorkShiftCD ALTER TABLE [dbo].[WorkOut] ALTER COLUMN [WorkShiftCD] NVARCHAR(40) COLLATE Cyrillic_General_CI_AS NULL
[dbo].[WorkOut] -> AbsenceCode ALTER TABLE [dbo].[WorkOut] ALTER COLUMN [AbsenceCode] VARCHAR(50) COLLATE Cyrillic_General_CI_AS NULL
[dbo].[WorkOut] -> PaymentType ALTER TABLE [dbo].[WorkOut] ALTER COLUMN [PaymentType] CHAR(4) COLLATE Cyrillic_General_CI_AS NULL
这篇关于如何将排序规则更改为数据库的所有列?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!