--碁煙 覦 譟郁覈
--碁煙 譟郁 15%伎
--伎 100 伎
-- 2005覯蟶..
set nocount on
set statistics io off
declare
@tablename varchar(255)
, @schemaname varchar(255)
, @fill_factor varchar(3);
set @fill_factor = '0';
if @fill_factor not between 1 and 100
set @fill_factor = '100';
if @@version like 'Microsoft SQL Server 2005%'
begin
--2005覯
declare tables cursor for
SELECT
TABLE_SCHEMA
, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
create table #fraglist2 (
database_id smallint NULL
, object_id int NULL
, index_id int NULL
, partition_number int NULL
, index_type_desc nvarchar(60) NULL
, alloc_unit_type_desc nvarchar(60) NULL
, index_depth tinyint NULL
, index_level tinyint NULL
, avg_fragmentation_in_percent float NULL
, fragment_count bigint NULL
, avg_fragment_size_in_pages float NULL
, page_count bigint NULL
, avg_page_space_used_in_percent float NULL
, record_count bigint NULL
, ghost_record_count bigint NULL
, version_ghost_record_count bigint NULL
, min_record_size_in_bytes int NULL
, max_record_size_in_bytes int NULL
, avg_record_size_in_bytes float NULL
, forwarded_record_count bigint NULL
)
create table #rs2 (
schema_name varchar(255)
, database_id smallint NULL
, object_id int NULL
, index_id int NULL
, partition_number int NULL
, index_type_desc nvarchar(60) NULL
, alloc_unit_type_desc nvarchar(60) NULL
, index_depth tinyint NULL
, index_level tinyint NULL
, avg_fragmentation_in_percent float NULL
, fragment_count bigint NULL
, avg_fragment_size_in_pages float NULL
, page_count bigint NULL
, avg_page_space_used_in_percent float NULL
, record_count bigint NULL
, ghost_record_count bigint NULL
, version_ghost_record_count bigint NULL
, min_record_size_in_bytes int NULL
, max_record_size_in_bytes int NULL
, avg_record_size_in_bytes float NULL
, forwarded_record_count bigint NULL
)
open tables
fetch next from tables into @schemaname, @tablename
while @@fetch_status = 0
begin
insert into #fraglist2
select *
from sys.dm_db_index_physical_stats(db_id(), object_id(@schemaname + '.' + @tablename), null, null, null)
insert into #rs2
select @schemaname, * from #fraglist2
truncate table #fraglist2
fetch next from tables into @schemaname, @tablename
end
select
企覈
, 碁煙る
, [朱Μ 蟆 譟郁 ]
, [蟆 伎]
, case when 碁煙る <> '' then '' else '--' end + [碁煙れ ろ襴渚] [碁煙れ ろ襴渚]
, case when 碁煙る <> '' then '' else '--' end + [碁煙れ^螳覈 ろ襴渚] [碁煙れ^螳覈 ろ襴渚]
from (
select
schema_name + '.' + object_name(object_id) 企覈
, (select name from sys.indexes where object_id = a.object_id and index_id = a.index_id) 碁煙る
, page_count [蟆 伎]
, cast(avg_fragmentation_in_percent as numeric(18,2)) [朱Μ 蟆 譟郁 ]
, case when avg_fragmentation_in_percent > 15 and page_count > 100 then 'ALTER INDEX ' + b.index_name + ' ON ' + schema_name + '.' + object_name(object_id) + ' REBUILD WITH (FILLFACTOR = ' + @fill_factor + ');' else '' end [碁煙れ ろ襴渚]
, case when avg_fragmentation_in_percent > 15 and page_count > 100 then 'ALTER INDEX ' + b.index_name + ' ON ' + schema_name + '.' + object_name(object_id) + ' REORGANIZE;' else '' end [碁煙れ^螳覈 ろ襴渚]
from #rs2 a outer apply(
select name index_name
from sys.indexes where object_id = a.object_id and index_id = a.index_id
) b
where object_name(object_id) not like 'sys%'
and schema_name <> 'sys'
and alloc_unit_type_desc = 'IN_ROW_DATA'
) t
order by 1
drop table #fraglist2
drop table #rs2
close tables
deallocate tables
end else
select @@version dbms_version, '2005覯 ' descriptions
go