-- Stored Procedure: dbo.GetShowContigForIndex
-- 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].[GetShowContigForIndex]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetShowContigForIndex]
GO

Create procedure dbo.GetShowContigForIndex
   @DatabaseName varchar(128)
 , @ObjectOwner varchar(128) = 'dbo'
 , @ObjectName varchar(128) = null
 , @IndexName varchar(128) = null
 , @PlanMaintenance tinyint = 8
as
/******************************************
* see IndexMaintenanceForDB
*
* status 
*  0 - under consideration
*  1 - defrag
*  2 - defrag in progress
*  3 - defrag complete
*  4 - reindex
*  5 - reindex in progress
*  6 - reindex complete 
*  7 - After defrag showcontig       (no-op)
*  8 - manually populate showcontig  (no-op)
*  9 - eliminated from consideration (no-op)
*  10 - 19 - historical 
*******************************************/
declare @SQLStr nvarchar(4000)
 , @ShowContigStartDt datetime

-- table should alrady exist unless call is one off
if object_id('tempdb.dbo.#fraglist','U') is null
 CREATE TABLE #fraglist 
  ( ObjectName varchar (255)
  , ObjectId INT
  , IndexName varchar (255)
  , IndexId INT
  , Lvl INT
  , CountPages INT
  , CountRows INT
  , MinRecSize INT
  , MaxRecSize INT
  , AvgRecSize INT
  , ForRecCount INT
  , Extents INT
  , ExtentSwitches INT
  , AvgFreeBytes INT
  , AvgPageDensity INT
  , ScanDensity DECIMAL
  , BestCount INT
  , ActualCount INT
  , LogicalFrag DECIMAL
  , ExtentFrag DECIMAL)

set @SQLStr = 'use ' 
            + @DatabaseName 
            + ' DBCC SHOWCONTIG([' 
            + @ObjectName 
            + '],[' 
            + @IndexName 
            + ']) WITH TABLERESULTS, FAST'

set @ShowContigStartDt = getdate()

insert #fraglist 
exec sp_executesql @SQLStr

insert admin.dbo.fraglist 
 ( DatabaseName
 , ObjectOwner
 , ObjectName
 , ObjectId
 , IndexName
 , IndexId
 , Lvl
 , CountPages
 , CountRows
 , MinRecSize
 , MaxRecSize
 , AvgRecSize
 , ForRecCount
 , Extents
 , ExtentSwitches
 , AvgFreeBytes
 , AvgPageDensity
 , ScanDensity
 , BestCount
 , ActualCount
 , LogicalFrag
 , ExtentFrag
 , Status
 , SecondsToShowContig)
select @DatabaseName
 , @ObjectOwner
 , ObjectName
 , ObjectId
 , IndexName
 , IndexId
 , Lvl
 , CountPages
 , CountRows
 , MinRecSize
 , MaxRecSize
 , AvgRecSize
 , ForRecCount
 , Extents
 , ExtentSwitches
 , AvgFreeBytes
 , AvgPageDensity
 , ScanDensity
 , BestCount
 , ActualCount
 , LogicalFrag
 , ExtentFrag
 , Status = 
   case 
    when @PlanMaintenance = 0 then
     case
      when (ObjectName = 'fraglist'
            or ScanDensity > 90
            or LogicalFrag < 10
            or (Indexid > 1 
                and exists(select 1 
                           from admin.dbo.fraglist 
                           where DatabaseName = @DatabaseName
                           and ObjectOwner = @ObjectOwner
                           and ObjectName = @ObjectName
                           and IndexId = 1 
                           and status = 4))) -- reindex clustered does all
       then 9  -- noop
      when CountPages < 10000
       then 4  -- reindex 
       else 1  -- defrag
       end
   else @PlanMaintenance
   end 
 , datediff(second,@ShowContigStartDt,getdate())
from #fraglist 

delete #fraglist
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON 
GO