-- Stored Procedure: dbo.DefragLargeTables
-- Bill Wunder

use admin
GO
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DefragLargeTables]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[DefragLargeTables]
GO

CREATE procedure dbo.DefragLargeTables
 @DatabaseName varchar(128)
as
/*
see IndexMaintenanceForDB
*/
declare @SQLStr nvarchar(4000)
 , @ObjectOwner varchar(128) 
 , @ObjectName varchar(255)
 , @IndexName varchar(255)
 , @status int
 , @DBCCStartDt datetime

-- one at a time until someone says stop, 2s before 1s
select top 1 @DatabaseName = Databasename
 , @ObjectOwner = ObjectOwner
 , @ObjectName = ObjectName
 , @IndexName = IndexName
 , @status = status
from admin.dbo.fraglist
where status in (1,2)
and DatabaseName = @DatabaseName
order by status desc

-- mark as current maintenance candidate if is a 1
update admin.dbo.fraglist		
set status = 2
where DatabaseName = @DatabaseName
and ObjectOwner = @ObjectOwner
and ObjectName = @ObjectName
and IndexName = @IndexName
and status = 1

set @SQLStr = 'DBCC INDEXDEFRAG(''' 
               + @DatabaseName 
               + ''','''
               + @ObjectOwner 
               + '.'
               + @ObjectName 
               + ''','''
               + @IndexName 
               + ''') ' +
               + 'UPDATE STATISTICS [' 
               + @DatabaseName 
               + '].[' 
               + @ObjectOwner 
               + '].[' + @ObjectName 
               + '] [' 
               + @IndexName 
               + ']'

set @DBCCStartDt = getdate()

exec sp_executesql @SQLStr 

-- mark as maintenance complete
update admin.dbo.fraglist		
set status = 3
 , SecondsToMaintain = Datediff(second,@DBCCStartDt,getdate()) 
where DatabaseName = @DatabaseName
and ObjectOwner = @ObjectOwner
and ObjectName = @ObjectName
and IndexName = @IndexName
and status = 2

-- add a fraglist row to show the table/index state after the maintenance
exec admin.dbo.GetShowContigForIndex 
   @DatabaseName = @DatabaseName
 , @ObjectOwner = @ObjectOwner
 , @ObjectName = @ObjectName
 , @IndexName = @IndexName
 , @PlanMaintenance = 7
GO
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO