mssql sqlserver 修改数据库字符集(排序规则)的脚本分享


摘要:
下文讲述通过sql脚本修改“指定数据库的”字符集(排序规则)的方法分享,如下所示:
实验环境:sqlserver 2008 R2


例:

   --修改字符集(排序规则)前,我们需关闭当前数据库的所有连接,然后再修改其字符集(排序规则),如下所示:
    use   master   
   
if   exists   (select   *   from   dbo.sysobjects   
where   id   =   object_id(N'[dbo].[pr_stopAllSpId]') 
  and   OBJECTPROPERTY(id,   N'IsProcedure')   =   1)    
drop   procedure   [dbo].pr_stopAllSpId      
GO    

 
create   proc   pr_stopAllSpId    
@dataBaseName   varchar(200) --待停止进程的数据库名称    
as       
        
declare   @spid    nvarchar(80)   
   
declare   #tb   cursor   for   
select   spid=cast(spid   as   varchar(80))   from   master..sysprocesses   where   dbid=db_id(@dataBaseName)    
open   #tb    
fetch   next   from   #tb   into   @spid    
while   @@fetch_status=0    
begin       
exec('kill   '+@spid)    ---关闭进程
fetch   next   from   #tb   into   @spid    
end       
close   #tb    
deallocate   #tb    
go 
--关闭对数据库"test"的连接    
exec   pr_stopAllSpId     'test'  
   
--修改数据库 "test" 字符集(排序规则)    
alter   database   [test]   COLLATE   Chinese_PRC_CI_AS_KS      
   
--修改完毕后删除临时存储过程  
if   exists   (select   *   from   dbo.sysobjects  
 where   id   =   object_id(N'[dbo].[pr_stopAllSpId]')   and   OBJECTPROPERTY(id,   N'IsProcedure')   =   1)    
drop   proc   [dbo].pr_stopAllSpId