​ Cannot resolve the collation conflict between​ "Thai_CI_AS" and "Thai_CI_AI"

ดึงข้อมูลจากหลาย database เจอ error

​[Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Cannot resolve the collation conflict between “Thai_CI_AS” and “Thai_CI_AI” in the UNION operation.

สาเหตุคือทั้ง 4 database มี table ชื่อเดียวกัน โครงสร้างเหมือนกัน แต่ collation ใช้ ต่างกัน แถมตรวจดูตารางอื่นๆ ก็ใช้กระจัดกระจาย Thai_CI_AI บ้าง Thai_CI_AS บ้าง ดีที่เจอ query ที่แก้ให้ได้ง่ายๆ เอามาดัดแปลงอักนิดให้เรียงตามชื่อตารางก็ใช้ง่ายเลย

DECLARE @collate SYSNAME
SELECT @collate = 'Thai_CI_AS'
SELECT
      '[' + SCHEMA_NAME(o.[schema_id]) + '].[' + o.name + '] -> ' + c.name AS a
    , '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 ('nchar', 'nvarchar') 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 WITH(NOLOCK)
JOIN sys.objects o WITH(NOLOCK) ON c.[object_id] = o.[object_id]
JOIN sys.types t WITH(NOLOCK) 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'
ORDER BY a

เอาผลลัพธ์ ใน column ที่ 2 ไป execute อีกครั้งก็ใช้ได้

ขอบคุณ Cr: Devart สำหรับคำตอบใน how to change the collate to all the columns of the database?