手工更改数据库字符集
在安装sql server的过程当中，经常会选错字符集，创建数据库之后，如果要更改，非常麻烦，一般多需要重装sql server，笔者尝试不重装sql，仅从数据库本身开始更改数据库层级的字符集。
第1步更改 更改数据库的默认字符集
alter database ETMIS_MDATA COLLATE Chinese_PRC_CI_AS
第2步更改 主键字段的字符集
Declare @Pk varChar( 100 );
declare @PkField varchar( 2000 );
declare @tbname varchar( 200 )
declare CUR_PKLIST CURSOR FOR
select distinct o. name ,i . name from sys . columns c ,sys . indexes i ,sys . index_columns ic ,sys . objects o
where c . object_id= i .object_id and c .object_id = ic. object_id and c .object_id = o. object_id
and c . collation_name= 'Chinese_PRC_BIN'
and c . column_id= ic .column_id
OPEN CUR_PKLIST
FETCH NEXT FROM CUR_PKLIST
INTO @tbname , @Pk
declare @sql varchar( 8000 )
set @sql = ''
WHILE @@FETCH_STATUS = 0
BEGIN
set @PkField = ''
select @PkField = @PkField+ ',' +c . name from sys . columns c ,sys . indexes i ,sys . index_columns ic ,sys . objects o
where c . object_id= i .object_id and c .object_id = ic. object_id and c .object_id = o. object_id
and o . name= @tbname and i. name =@Pk
and c . column_id= ic .column_id
set @PkField =right( @PkField, len (@PkField )- 1)
set @sql = @sql + ' Alter table ' + @tbname + ' Drop ' + @Pk
set @sql =@sql +' go '
SELECT @sql = @sql + ' alter table ' +o . name + ' alter column ' + c . name + ' VARCHAR('+ convert ( varchar (10 ), c. max_length )+') COLLATE Chinese_PRC_CI_AS '
from sys .columns c, sys .indexes i, sys .index_columns ic, sys .objects o
where c . object_id= i .object_id and c .object_id = ic. object_id and c .object_id = o. object_id
and c . collation_name= 'Chinese_PRC_BIN'
and o . name= @tbname and i. name =@Pk
set @sql = @sql + ' go '
SELECT @sql = @sql + ' ALTER Table ' + @tbname + ' ADD CONSTRAINT ' + @Pk +' PRIMARY KEY (' +@PkField +')'
set @sql =@sql +' go '
FETCH NEXT FROM CUR_PKLIST
INTO @tbname , @Pk
end
close CUR_PKLIST
deallocate CUR_PKLIST
select @sql
第3步更改 非主键字段的字符集
SELECT 'alter table ' +b .name +' alter column ' + a .name +' VARCHAR('+convert( varchar(10 ),a. length)+') COLLATE Chinese_PRC_CI_AS '
FROM SYSCOLUMNS a ,sysobjects b
WHERE a.collation ='Chinese_PRC_BIN'
and a.type =39
and a.id =b. id and b. xtype='u'