该 SQL 索引碎片整理脚本,–一 、SQL获取中文字符串对应的拼音首字母字符串函数

/*******************************************************/
/*         功用:SQL Server 2006索引碎片整理            */
/*              逻辑碎片>=30重建索引,<30再度协会索引   */
/*         作者:贾桂军                                 */
/*         日期:2008年6月11日                          */
/*******************************************************/
/***********SQL Server
二〇〇七索引碎片整理*****************/
/**选择办法:将索要整理索引碎片的数据库设置为如今数据库**/
set nocount on
–使用游标重新组织钦赐库中的索引,化解索引碎片
–R_T层游标取出当前数据库全部表
declare R_T cursor
for select name from sys.tables
declare @T varchar(50)
open r_t
fetch next from r_t into @t
while @@fetch_status=0
begin
–R_index游标判断钦点表索引碎片境况并优化
declare R_Index cursor
for select t.name,i.name,s.avg_fragmentation_in_percent from
sys.tables t
  join sys.indexes i on i.object_id=t.object_id
  join
sys.dm_db_index_physical_stats(db_id(),object_id(@T),null,null,’limited’)
s
   on s.object_id=i.object_id and s.index_id=i.index_id
declare @TName varchar(50),@IName varchar(50),@avg int,@str
varchar(500)
open r_index
fetch next from r_index into @TName,@Iname,@avg
while @@fetch_status=0
begin
  if @avg>=30  –假使碎片大于30,重建索引
  begin
   set @str=’alter index ‘+rtrim(@Iname)+’ on dbo.’+rtrim(@tname)+’
rebuild’
  end
  else   –要是碎片小于30,重新协会目录
  begin
   set @STR=’alter index ‘+rtrim(@Iname)+’ on dbo.’+rtrim(@tname)+’
reorganize’
  end
  print @str
  exec (@str)  –执行
  fetch next from r_index into @TName,@Iname,@avg
end
–结束r_index游标
close r_index
deallocate r_index
fetch next from r_t into @t
end
–结束R_T游标
close r_t
deallocate r_t
set nocount off

 

SQL批量重命名普通话字段名为对应拼音首字母字段名脚本

/*
–查看内定表的目录意况
select t.name,i.name,s.avg_fragmentation_in_percent from sys.tables
t
join sys.indexes i on i.object_id=t.object_id
join
sys.dm_db_index_physical_stats(db_id(),object_id(‘t2′),null,null,’limited’)
s
  on s.object_id=i.object_id and s.index_id=i.index_id

SQL Server 贰零零柒:索引碎片整理脚本。

 

*/

第③表明:该 SQL 索引碎片整理脚本,是从 SQL Server 二〇〇六联机补助上摘录下来,并且稍加整理而成的。

操作步骤1:生成获取汉语字符串对应的拼音首字母字符串函数

该 SQL 索引碎片整理脚本,首先从 SQL Server 2007 系统一管理理视图
sys.dm_db_index_physical_stats 中,找出索引碎片程度大于 十分之一的目录,然后根据目录碎片程度,分别来采用分歧的章程来整理索引碎片。小于
百分之三十 的应用 alter index reorganize;大于等于 三成 的应用 alter index
rebuild。当中 reorganize 相当于 dbcc indexdefrag();rebuild 也就是 dbcc
dbreindex()。

操作步骤2:执行重命名中文字段名为拼音字首母字段名脚本

SQL 碎片整理后,索引数据页在数据库文件中排列的更紧密,能够大幅升高一些
SQL 查询的频率。DBA
能够周周进行1回碎片整理。此外要留心的是,不要在减少数据库(dbcc
shrinkfile, dbcc shrinkdatabase)前整理索引碎片。

–壹 、SQL获取普通话字符串对应的拼音首字母字符串函数

Using sys.dm_db_index_physical_stats in a script to rebuild or
reorganize indexes
The following example automatically reorganizes or rebuilds all
partitions in a database that have an average fragmentation over 10
percent. Executing this query requires the [view database state]
permission.

Create  function fun_getPY 


 ( 

— ensure a USE  statement has been executed first.

set nocount on

declare @objectid         int
       ,@indexid          int
       ,@partitioncount   bigint
       ,@schemaname       sysname
       ,@objectname       sysname
       ,@indexname        sysname
       ,@partitionnum     bigint
       ,@partitions       bigint
       ,@frag             float
       ,@command          varchar(1000)

select objectid     = object_id
      ,indexid      = index_id
      ,partitionnum = partition_number
      ,frag         = avg_fragmentation_in_percent
  into #work_to_do
  from sys.dm_db_index_physical_stats(db_id(), null, null , null,
‘LIMITED’)
where avg_fragmentation_in_percent > 10.0
   and index_id > 0

— declare the cursor for the list of partitions to be processed.
declare partitions cursor for
   select * from #work_to_do

— Open the cursor.
open partitions

— Loop through the partitions.
fetch next from partitions into @objectid, @indexid, @partitionnum,
@frag

while @@fetch_status = 0 begin
   select @objectname = o.name, @schemaname = s.name
     from sys.objects as o
            inner join sys.schemas as s
      on s.schema_id = o.schema_id
   where o.object_id = @objectid

   select @indexname = name
     from sys.indexes
    where object_id = @objectid
      and index_id = @indexid

   select @partitioncount = count (*)
    from sys.partitions
   where object_id = @objectid
     and index_id = @indexid

   — 30 is an arbitrary decision point at which to switch between
reorganizing and rebuilding
   if @frag < 30.0 begin
       select @command = ‘alter index ‘ + @indexname + ‘ on ‘
                       + @schemaname + ‘.’ + @objectname + ‘
reorganize’

       if @partitioncount > 1
           select @command = @command + ‘ partition=’ + convert(char,
@partitionnum)
   end

   if @frag >= 30.0 begin
       select @command = ‘alter index ‘ + @indexname +’ on ‘
                       + @schemaname + ‘.’ + @objectname + ‘ rebuild’

       if @partitioncount > 1
           select @command = @command + ‘ partition=’ + convert(char,
@partitionnum)
   end

   — exec (@command)
   print ‘Executed: ‘ + @command

   fetch next from partitions into @objectid, @indexid, @partitionnum,
@frag
end

— free resource
close partitions
deallocate partitions
drop table #work_to_do
《SQL Server 二零零六:索引碎片整理脚本》代码摘自 SQL Server 2007 Books
Online。

图片 1 爱情是五个人的”饰“!
欢迎光临我的时髦饰品店:http://shop36465575.taobao.com

**[莫不有出人意料的获得!!!**](http://shop36465575.taobao.com/)

    @str nvarchar(4000) 

 ) 

returns nvarchar(4000) 

as 

begin 

  declare @word nchar(1),@PY nvarchar(4000)

  set @PY=”

  while len(@str)>0 

  begin 

    set @word=left(@str,1)

    –假使非汉字字符,再次来到原字符 

    set @PY=@PY+(case when unicode(@word) between 19968 and 19968+20901 

               then (  

                            select top 1 PY  

                            from  

                            (  

                             select ‘A’ as PY,N’驁’ as word 

                             union all select ‘B’,N’簿’ 

                             union all select ‘C’,N’錯’ 

        union all select ‘D’,N’鵽’ 

        union all select ‘E’,N’樲’ 

        union all select ‘F’,N’鰒’ 

        union all select ‘G’,N’腂’ 

        union all select ‘H’,N’夻’ 

        union all select ‘J’,N’攈’ 

        union all select ‘K’,N’穒’ 

        union all select ‘L’,N’鱳’ 

        union all select ‘M’,N’旀’ 

        union all select ‘N’,N’桛’ 

        union all select ‘O’,N’漚’ 

        union all select ‘P’,N’曝’ 

        union all select ‘Q’,N’囕’ 

        union all select ‘R’,N’鶸’ 

        union all select ‘S’,N’蜶’ 

        union all select ‘T’,N’籜’ 

        union all select ‘W’,N’鶩’ 

        union all select ‘X’,N’鑂’ 

        union all select ‘Y’,N’韻’ 

        union all select ‘Z’,N’咗’ 

                      ) T  

                   where word>=@word collate
Chinese_PRC_CS_AS_KS_WS  

                   order by PY ASC 

                          )  

                      else @word  

                 end) 

    set @str=right(@str,len(@str)-1) 

  end

  return @PY

end

 

 

–二 、SQL批量重命名汉语字段名为对应拼音首字母字段名脚本

 

–查询当前数据库中的全部汉语字段名列表

select b.name as tablename,a.name as columnname from sys.columns a left
join sys.objects b on a.object_id=b.object_id  where UNICODE(a.name)
BETWEEN 19968 AND 19968+20901 order by b.name

–定义游标查询变量

declare @TableName nvarchar(250)

declare @ColumnName nvarchar(250)

–注解读取数据库全部数据表名称游标mycursor1

declare mycursor1 cursor for select b.name as tablename,a.name as
columnname from sys.columns a left join sys.objects b on
a.object_id=b.object_id  where UNICODE(a.name) BETWEEN 19968 AND
19968+20901 order by b.name

 –打开游标

open mycursor1 

–从游标里取出数据赋值到大家刚刚表明的数额表名变量中

fetch next from mycursor1 into @TableName,@ColumnName

–假如游标执行成功  

while (@@fetch_status=0) 

begin 

 –定义重命名字段变量

declare @NewColumnName varchar (250)

select @NewColumnName=dbo.fun_getPY(@ColumnName)

declare @renameCommand nvarchar(2000)

set @renameCommand=@TableName+’.’+@ColumnName

–钦命重命名字段指令

exec sp_rename @renameCommand,@NewColumnName,’column’

 –用游标去取下一条记下

    fetch next from mycursor1 into @TableName,@ColumnName

end

–关闭游标

close mycursor1 

–撤废游标

deallocate mycursor1

–查询重命名后的眼下数据库中的全数中文字段名列表

select b.name as tablename,a.name as columnname from sys.columns a left
join sys.objects b on a.object_id=b.object_id  where UNICODE(a.name)
BETWEEN 19968 AND 19968+20901 order by b.name

 

相关文章