修改后的索引整理存储过程--加入的去重

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[P_ReIndex]    Script Date: 01/07/2013 16:54:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[P_ReIndex]
as
begin
declare @s varchar(8000)=
'create table #t( 编号 int,名称 varchar(255),sch varchar(255),tbl varchar(255),avg_fragmentation_in_percent float,t varchar(8000))
insert #t(编号 ,名称 ,sch ,tbl,avg_fragmentation_in_percent,t)
SELECT  a.index_id 编号,b.NAME,c.sch,c.tbl,avg_fragmentation_in_percent,
       (case when avg_fragmentation_in_percent>30
        then ''
        use [?]
        begin try
        ALTER INDEX [''+b.NAME+''] ON [''+c.sch+''].[''+c.tbl+''] REBUILD WITH (ONLINE = ON)
        print ''''重新生成：?   [''+c.sch+''].[''+c.tbl+'']:[''+b.NAME+'']成功！''''
        end try
        begin catch
        print ''''重新生成：?   [''+c.sch+''].[''+c.tbl+'']:[''+b.NAME+'']失败！''''
        print ''''失败：''''+error_message()
        end catch
        ''
        else ''
        use [?]
        begin try
        ALTER INDEX [''+b.NAME+''] ON [''+c.sch+''].[''+c.tbl+''] REORGANIZE
        print ''''重新组织：?   [''+c.sch+''].[''+c.tbl+'']:[''+b.NAME+'']成功！''''
        end try
        begin catch
        print ''''重新组织：?   [''+c.sch+''].[''+c.tbl+'']:[''+b.NAME+'']失败！''''
        print ''''失败：''''+error_message()
        end catch
        ''
        end)
FROM    [?].sys.dm_db_index_physical_stats(db_id(''?''),NULL, NULL,NULL,NULL) a
        JOIN [?].sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id
join (select b.name sch,a.name tbl,a.object_id from [?].sys.all_objects a inner join [?].sys.schemas b
on a.schema_id=b.schema_id and a.type=''u'' ) c
  on a.object_id=c.object_id
WHERE NAME IS NOT NULL AND avg_fragmentation_in_percent > 5
select t into #t2 from
(select *,rid=row_number() over(partition by 编号 ,名称 ,sch ,tbl order by avg_fragmentation_in_percent desc) from #t ) as aa
where rid=1
declare @s varchar(8000)
declare cu_idx cursor for
select t from #t2
open cu_idx
fetch next from cu_idx into @s
while @@FETCH_STATUS=0
begin
exec(@s)
fetch next from cu_idx into @s
end
close cu_idx
deallocate cu_idx
drop table #t,#t2
'
exec sp_MSforeachdb @s
--print len(@s)
end