다른 곳에서 전체 백업된 SQL을 받아 데이터베이스를 생성했는데, 생성했더니, 데이터베이스의 Data Collate가 "Latin1_General_CI_AS"로 설정되어 있었다. 그래서 간신히 데이터베이스의 설정 값을 바꾸어 "Korean_Wansung_CI_AS"으로 바꾸긴 했다. 그런데 Query를 실행하니까, 웬걸...컬럼들은 여전히 "Latin1_General_CI_AS" 로 되어 있었고, 새로 만든 테이블 내의 값들과 비교하려는데 자꾸 다음과 같은 에러메시지를 뱉어됐다.
Cannot resolve the collation conflict between "Korean_Wansung_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.데이블 디자이너, 그러니까, 테이블 수정에 들어가 varchar 컬럼마다 데이터 정렬(Data Collate) 값을 바꾸면 되긴 하는데, 이 많은 테이블의 또, 그 컬럼들에 들어가 수정하려니 깝깝했다.
그래서 구글을 통해서 이런 저런 검색한 결과, stackOverflow에서 찾았다.
Change collations of all columns of all tables in SQL Server
I imported a database with some data to compare with another database. The target database has collation Latin1_General_CI_AS and the source database has SQL_Latin1_General_CP1_CI_AS. I did change ...
stackoverflow.com
아래의 코드 값에서 @collate값만 Korean_Wansung_CI_AS로 변경하고, 수정할 데이터베이스의 쿼리창을 열고 실행했다.
DECLARE @collate nvarchar(100);
DECLARE @table nvarchar(255);
DECLARE @column_name nvarchar(255);
DECLARE @column_id int;
DECLARE @data_type nvarchar(255);
DECLARE @max_length int;
DECLARE @row_id int;
DECLARE @sql nvarchar(max);
DECLARE @sql_column nvarchar(max);
SET @collate = 'Korean_Wansung_CI_AS';
DECLARE local_table_cursor CURSOR FOR
SELECT [name]
FROM sysobjects
WHERE OBJECTPROPERTY(id, N'IsUserTable') = 1
OPEN local_table_cursor
FETCH NEXT FROM local_table_cursor
INTO @table
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE local_change_cursor CURSOR FOR
SELECT ROW_NUMBER() OVER (ORDER BY c.column_id) AS row_id
, c.name column_name
, t.Name data_type
, c.max_length
, c.column_id
FROM sys.columns c
JOIN sys.types t ON c.system_type_id = t.system_type_id
LEFT OUTER JOIN sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
LEFT OUTER JOIN sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
WHERE c.object_id = OBJECT_ID(@table)
ORDER BY c.column_id
OPEN local_change_cursor
FETCH NEXT FROM local_change_cursor
INTO @row_id, @column_name, @data_type, @max_length, @column_id
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@max_length = -1) OR (@max_length > 4000) SET @max_length = 4000;
IF (@data_type LIKE '%char%')
BEGIN TRY
SET @sql = 'ALTER TABLE ' + @table + ' ALTER COLUMN ' + @column_name + ' ' + @data_type + '(' + CAST(@max_length AS nvarchar(100)) + ') COLLATE ' + @collate
PRINT @sql
EXEC sp_executesql @sql
END TRY
BEGIN CATCH
PRINT 'ERROR: Some index or constraint rely on the column' + @column_name + '. No conversion possible.'
PRINT @sql
END CATCH
FETCH NEXT FROM local_change_cursor
INTO @row_id, @column_name, @data_type, @max_length, @column_id
END
CLOSE local_change_cursor
DEALLOCATE local_change_cursor
FETCH NEXT FROM local_table_cursor
INTO @table
END
CLOSE local_table_cursor
DEALLOCATE local_table_cursor
GO지금 모든 데이터베이스의 모든 테이블의 컬럼들을 한번에 수정할 수 있었다.
다행다행...
하인도1
[하인드/하인도/인도짱 의 홈페이지] 저만의 공간입니다. 다양한 소재들을 나열하는 아주 단순 무식한 홈페이지 입니다. 다양한 문서 자료도 있겠지만, 저의 푸념들도 있답니다.
