查找SQL Server中所有表的列上的非默认排序

Is there a script I can use to find all columns in all tables in a SQL catalog that does not have the collation <database default> ?

是否有一个脚本可以用于在SQL目录中查找所有表中的所有列，而SQL目录中没有collation ?

I have taken over a legacy system and have different collations in the some tables and I would like to find all instances in one hit rather than going through manually.

我已经接管了一个遗留系统，在一些表中有不同的排序，我希望在一个hit中找到所有实例，而不是手工进行。

However I don't want to change the collation programmatically as I would like to review each table one by one.

但是，我不想以编程的方式更改排序规则，因为我想逐个检查每个表。

1 个解决方案

#1

Try this script here:

试试这个脚本:

DECLARE @DatabaseCollation VARCHAR(100)

SELECT 
    @DatabaseCollation = collation_name 
FROM 
    sys.databases
WHERE 
    database_id = DB_ID()

SELECT 
    @DatabaseCollation 'Default database collation'

SELECT 
    t.Name 'Table Name',
    c.name 'Col Name',
    ty.name 'Type Name',
    c.max_length,
    c.collation_name,
    c.is_nullable
FROM 
    sys.columns c 
INNER JOIN 
    sys.tables t ON c.object_id = t.object_id
INNER JOIN 
    sys.types ty ON c.system_type_id = ty.system_type_id    
WHERE 
    t.is_ms_shipped = 0
    AND 
    c.collation_name <> @DatabaseCollation

It checks for the database default collation and then finds any columns that don't comply with that.

它检查数据库默认的排序，然后找到任何不符合这个的列。