SQL SERVER DOESN'T CHANGE COLLATION

Rabia Kurnaz 386 Reputation points
2023-10-20T08:30:57.2066667+00:00

I want to change db collation. But i didn't change please help me

Msg 1505, Level 16, State 1, Line 3

The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.sysschobjs' and the index name 'nc1'. The duplicate key value is (0, 1, Hasta_Karti).

Msg 5072, Level 16, State 1, Line 3

ALTER DATABASE failed. The default collation of database 'DatabaseName cannot be set to Turkish_CI_AS.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,924 questions
0 comments No comments
{count} votes

Accepted answer
  1. Erland Sommarskog 102.4K Reputation points
    2023-10-23T20:58:55.7733333+00:00

    I'm afraid that there are several issues with Cosmog's query. The system table sysschobjs is not visible in a normal connection only on the user connection. It does not have a column called ColumnName. And if you take the name column instead, the query would only returns rows if there objects in different schema with the same name, which is not an issue. You must cast to the new collation to find the would-be duplicates.

    Here is a tested query:

    ; WITH CTE AS (
        SELECT s.name AS "schema", o.name AS object, o.type, 
               cnt = COUNT(*) OVER(PARTITION BY s.name COLLATE Turkish_CI_AS, o.name COLLATE Turkish_CI_AS)
        FROM   sys.objects o
        JOIN   sys.schemas s ON o.schema_id = s.schema_id
    )
    SELECT "schema", object, type, cnt
    FROM   CTE
    WHERE  cnt > 1
    ORDER  BY "schema", object
    
    

2 additional answers

Sort by: Most helpful
  1. Dan Guzman 9,216 Reputation points
    2023-10-20T11:14:40.3233333+00:00

    What is the current databases collation?

    If the existing database collation is case-sensitive, you'll need to first rename objects (and columns within the same table/view/table-valued function) that differ only by case to avoid duplicates in a case-insensitive system catalog.

    For example:

    CREATE DATABASE CollationTest COLLATE Turkish_CS_AS;
    GO
    
    USE CollationTest;
    CREATE TABLE dbo.Hasta_Karti(col1 int);
    CREATE TABLE dbo.hasta_karti(col1 int);
    GO
    
    --this fails with CREATE UNIQUE INDEX error
    ALTER DATABASE CollationTest COLLATE Turkish_CI_AS;
    GO
    
    --rename table to avoid duplicate object name
    EXEC sp_rename N'dbo.hasta_karti', 'hasta_karti2';
    GO
    
    --this now succeeds
    ALTER DATABASE CollationTest COLLATE Turkish_CI_AS;
    GO
    

  2. CosmogHong-MSFT 23,946 Reputation points Microsoft Vendor
    2023-10-23T01:51:18.2133333+00:00

    Hi @Rabia Kurnaz

    Use below query to find duplicate values.

    SELECT ColumnName, COUNT(*)
    FROM dbo.sysschobjs
    GROUP BY ColumnName
    HAVING COUNT(*) > 1
    

    Best regards,

    Cosmog Hong


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.