Maystyle :
Admin : New post
Guestbook
Local
media
Catergories
Recent Articles
Recent Comments
Recent Trackbacks
Calendar
Tag
Archive
Link
Search
 
  그냥 Collate 바꾸다가 심심해서 만들어봅니다. 
작성일시 : 2010. 8. 4. 12:27 | 분류 : SQL Server/Administration

create table t2 ( a varchar(30))
create table t3 ( a varchar(30) collate Korean_Wansung_CS_AS)

select a.name as table_name, b.name as column_name, b.collation_name
    from sys.objects as a join sys.all_columns as b on a.object_id = b.object_id
    where a.name = 't2'
select a.name as table_name, b.name as column_name, b.collation_name
    from sys.objects as a join sys.all_columns as b on a.object_id = b.object_id
    where a.name = 't3'

ALTER TABLE t3 ADD CONSTRAINT CK_TEST
    CHECK (a = 'A')
select 'ALTER TABLE ' + (select name from sys.objects where object_id=a.parent_object_id) + ' DROP CONSTRAINT ' + a.name
    from sys.objects as a
    where type_desc = 'CHECK_CONSTRAINT'

CHECK CONSTRAINT는 스크립트 생성기에서 발췌하면 되는 PK와 FK 제약 조건은 그렇게 하면 잘 않되는 군요… 작업일에는 그냥 손으로 다 발췌했지만, 자동화를 시키긴 시켜야 할꺼 같습니다.

select * from sys.columns where collation_name = 'Korean_Wansung_CS_AS'
select * from INFORMATION_SCHEMA.COLUMNS

select 'alter table ' + a.name + ' alter column ' +
    (select data_type + '(' + CAST(character_maximum_length AS varchar(10))+')'
        from INFORMATION_SCHEMA.COLUMNS
        where TABLE_NAME = a.name and COLUMN_NAME = b.name)
    + ' collate Korean_Wansung_CS_AS'
    from sys.objects as a join sys.all_columns as b on a.object_id = b.object_id
    where b.collation_name != 'Korean_Wansung_CI_AS' and a.name like 't%'

select a.name as table_name, b.name as column_name, b.collation_name
    from sys.objects as a join sys.all_columns as b on a.object_id = b.object_id
    where b.collation_name != 'Korean_Wansung_CI_AS' and a.name like 't%'

ALTER DATABASE aaa SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE aaa COLLATE Korean_Wansung_CS_AS
ALTER DATABASE aaa SET MULTI_USER

|