MSSQL Server Veritabanı ve Tabloların Collation Değişimi

Bu makalede veritabanı ve tabloların collation değiştirme işlemini ele alacağız. Bazen kullanıcılar veritabanının yanlış collation ile oluşturulduğunu düşünürler. Bunun için veritabanının collation değişikliği için talep de bulunurlar. Ya da veritabanında bulunan tablo veya ilgili kolonun collation yapısını veritabanından bağımsız bir şekilde değiştirilmesini isteyebilirler. Bu makalemizde böyle durumlarda neler yapılır değinelim.

Veritabanı collation değişikliği veritabanı altında bulunan tablolarda collation değişimine sebep olmaz.

Bu örneğimizi DENEME veritabanımız üzerinde uygulamalı bir şekilde görelim. Veritabanımızın hangi collation’da olduğunu properties ekranından General sekmesinde görebiliriz.

Veya aşağıdaki komutlar yardımıyla da öğrenebiliriz.

1. Komut

USE master
GO
SELECT DATABASEPROPERTYEX('AdventureWorks2012','COLLATION')

2. Komut

select collation_name from sys.databases where name='AdventureWorks2012'

İlk olarak veritabanı bazında Collation nasıl değiştirilir bunu görelim. Veritabanına sağ tıklanıp Properties ekranında Options kısmında Collation bölümünde yeni Collation değerimizi seçiyoruz.

Yeni collation değerini ayarladıktan sonra yaptığımız işlemin Script’ini alıyoruz. Kodumuzun başına veritabanımızı single user mod kod bloğunu ekliyoruz. Yoksa veritabanı kullanılıyor diye bir hata mesajı alırız.

USE master
GO
ALTER DATABASE [DENEME] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [DENEME]
GO
ALTER DATABASE [DENEME] COLLATE Turkish_CI_AS
GO
GO
ALTER DATABASE [DENEME] SET MULTI_USER

Singel_user moda alınırken ilgili mod başka bir session tarafından alınabilir. Aşağıdaki komut aracılığıyla veritabanı üzerinde bağlı olan oturumları görebiliriz. Bu komutla ilgili oturumlar kill edilebilir.

SELECT spid, loginame, status, hostname, program_name FROM 
sys.sysprocesses WHERE dbid = DB_ID('DB_NAME');

İkinci bir komut:

select 'kill '+CAST(spid AS varchar(50)) from sys.sysprocesses where dbid=DB_ID('DB_NAME')

Ayrıca database’i singel user moda aldıktan sonra collation değişiminde şöyle bir hata mesajıyla karşılaşmış oluruz. SCHEMABINDING seçeneği kullanılarak oluşturulmuş bir VIEW veya FUNCTION, veritabanının collation ayarını kullanıyorsa, bu ayar değiştirilemez. ALTER DATABASE ile collation değiştirmek için, önce bu bağımlılığı kaldırmanız gerekir.

The object ‘stringSplitter’ is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.
Msg 5072, Level 16, State 1, Line 10
ALTER DATABASE failed. The default collation of database ‘DB_NAME’ cannot be set to Turkish_CI_AS.

İlgili function ve view altına bakarak belirtilen ifadenin silinip tekrardan oluşturulması gerekmektedir. Değişiklikten sonra.

Aşağıdaki sorgu, schema-bound nesneleri listeler. Gelen sonuca göre ilgili ifadeler drop edilir.

SELECT name, type_desc 
FROM sys.objects 
WHERE OBJECTPROPERTY(object_id, 'IsSchemaBound') = 1;

Bir diğer kullanacağımız komut:

SELECT name, definition
FROM sys.sql_modules m
JOIN sys.objects o ON m.object_id = o.object_id
WHERE m.definition LIKE '%SCHEMABINDING%';

Collation değişimi sırasında ikinci karşılaşacağımız hata veritabanı altında bulunan bazı constrant yapılarının collation ile bağlantısının olmasıdır. Öncelikle bu constrantların kaldırılması gerekmektedir.

Msg 5075, Level 16, State 1, Line 10
The object ‘CK_Product_ProductLine’ is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.

Collation değişikliği yaptıktan sonra veritabanımız üzerinden kontrol işlemi yapıyoruz.

Veritabanı bazında collation değişikliğinin gerçekleştiğini görüyoruz. Şimdi veritabanı altında bulunan tablomuzun Collation’ı değişmiş mi bunu gözlemleyelim. Aşağıdaki Kod bloğunda ilgili veritabanı altındaki tabloların Collation yapısını görebiliriz.

SELECT t.name TableName, c.name ColumnName, collation_name  
FROM sys.columns c  
inner join sys.tables t on c.object_id = t.object_id

Bir başka veritabanı altında bulunan tabloların collation yapısını bulmak için aşağıdaki komut kullanılmaktadır.

SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLLATION_NAME IS NOT NULL
ORDER BY TABLE_NAME;

Veritabanı değişikliği öncesinde oluşturulan tablonun Collation yapısı değişmemiş. Yukarıdaki resimde null ifadesi veri  türünün int olmasından kaynaklanmaktadır.

Şimdi aynı veritabanı altında yeni bir tablo oluşturalım. Yeni bir tablo oluşturduktan sonra collation yapısının güncel veritabanı collation yapısıyla aynı olduğu görülmektedir.

Önceki collation altında oluşturulmuş olan tabloları yeni collation yapısını geçirmek için aşağıdaki komut kullanılmaktadır. Burada yapmamız gereken NewCollation  kısmında veritabanı ile uyumlu collation yazmamız gerekiyor.

declare @NewCollation sysname
set @NewCollation = 'Turkish_CI_AS'
select
    'ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(stable.schema_id)) + '.' + QUOTENAME(stable.name) +
    ' ALTER COLUMN ' + QUOTENAME(scol.name) + ' ' + stype.name + '(' +
    CASE WHEN scol.max_length = -1 THEN 'max' ELSE CONVERT(varchar(10),scol.max_length) END +
    ') collate ' + @NewCollation + '
    go
    '
from sys.columns scol inner join sys.tables stable    on scol.object_id = stable.object_id
        inner join sys.types stype on scol.user_type_id = stype.user_type_id
where  scol.collation_name is not null and OBJECTPROPERTY(stable.object_id,N'IsMSShipped')=0

Gelen ekranda her kolunla ilgili değişiklik yapacağımız Script geliyor. Bunların teker teker kopyalanıp çalıştırılması gerekmektedir.

Çalıştırıldıktan sonra ilgili tablolarımızın yeni Collation yapısına geçtiği gözlemlenir.

Aşağıdaki komut ile ilgili tablo altındaki herhangi bir kolunun Collation yapısını vermektedir.

USE deneme
GO
SELECT name, collation_name
FROM sys.columns
WHERE OBJECT_ID IN (SELECT OBJECT_ID
FROM sys.objects
WHERE type = 'U'
AND name = 'TABLE_NAME')
AND name = 'COLUMN_NAME'

Ya da SSMS arayüzün de ilgili kolunun Properties ekranında görebilirsiniz.

Not: Bazı kolonlarda collation değişimi sırasında hata alınabilir. Belirtilen linkteki ilgili Makalenin okunması gerekmektedir.

Bu makalede veritabanı Collation yapısı nasıl değiştirilir konusunu görmüş olduk.

Başka bir makalede görüşmek dileğiyle..

Onlar, ” Yalnızca Allah’a Dayanıp Güvenirler. ” Mücadele-10