SQL SERVER DOESN'T CHANGE COLLATION

Question

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.

Answer 1

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

Answer 2

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

Answer 3

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.