--dbcc showcontig with tableresults
--dbcc showcontig with tableresults, all_indexes

set nocount on

declare @cmd varchar(256)

create table #dbreindex (
    dbname sysname not null,
    tablename sysname not null primary key,
    [started] datetime default(getdate()),
    altered datetime default(null),
    logsize int default(null),
    logsize2 int default(null)
)

create table #dbcc_showcontig (
    ObjectName  sysname,
    ObjectId    int,
    IndexName   sysname,
    IndexId     smallint,
    Level       smallint,
    Pages       int,
    Rows        int,
    MinimumRecordSize   int,
    MaximumRecordSize   int,
    AverageRecordSize   float,
    ForwardedRecords    int,
    Extents int,
    ExtentSwitches  int,
    AverageFreeBytes    float,
    AveragePageDensity  float,
    ScanDensity float,
    BestCount   int,
    ActualCount int,
    LogicalFragmentation    int,
    ExtentFragmentation int
)
select top 1 * into #dbcc_showcontig_altered  from #dbcc_showcontig where 1 = 1

insert into #dbcc_showcontig exec ('dbcc showcontig with tableresults')

exec sp_MSforeachtable @command1 = '
    print ''?''
    declare @logsize int
    declare @dbname sysname
    set @dbname = db_name()
    insert into #dbreindex (dbname, tablename ) values (@dbname, ''?'')
    select @logsize = sum(size) from sysfiles where groupid = 0
    update #dbreindex set logsize = @logsize where tablename = ''?'' and dbname = @dbname
    --dbcc dbreindex(''?'','''',80)
    begin try
        alter index all on ? rebuild with(sort_in_tempdb = on, maxdop = 8)
    end try
    begin catch
        print ''? のインデックス操作に失敗しました。''
    end catch
    update #dbreindex set altered = getdate() where tablename = ''?'' and dbname = @dbname
    select @logsize = sum(size) from sysfiles where groupid = 0
    update #dbreindex set logsize2 = @logsize where tablename = ''?'' and dbname = @dbname
'

insert into #dbcc_showcontig_altered exec ('dbcc showcontig with tableresults')

set @cmd = 'backup log '+cast(db_name() as varchar)+' with truncate_only'
exec (@cmd)

select datediff(ms, started, altered) as time_msec, (logsize2 - logsize) as growth, * from #dbreindex
select datediff(s, min(started), max(altered)) as total_sec from #dbreindex
select * from #dbcc_showcontig
select * from #dbcc_showcontig_altered



drop table #dbreindex
drop table #dbcc_showcontig
drop table #dbcc_showcontig_altered