use Master
go

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

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

CREATE TABLE [dbo].[tb_FragTrack_log] (
	[Record_id] [int] IDENTITY (1, 1) NOT NULL ,
	[Date_time] [datetime] NOT NULL ,
	[DBName] [varchar] (50) NOT NULL ,
	[Tablename] [varchar] (50) NOT NULL ,
	[TableRows] [int] NOT NULL ,
	[PagesScnd] [int] NOT NULL ,
	[ExtentsScnd] [int] NOT NULL ,
	[ExtentSws] [int] NOT NULL ,
	[AvgPagesExt] [real] NOT NULL ,
	[ScanDensity] [real] NOT NULL ,
	[ExtRatio] [varchar] (20) NOT NULL ,
	[LogicalScnFrag] [real] NOT NULL ,
	[ExtScanFrag] [real] NOT NULL ,
	[AvgByteFree] [real] NOT NULL ,
	[AvgPageDens] [real] NOT NULL 
) ON [PRIMARY]
GO

CREATE Procedure dg_TableFragmentation @db varchar(50), @table varchar(50)

as

/** 	This Procedure will Run the DBCC SHOWCONTIG command against the table specified in the database specified.
	The data from the DBCC Command is augmented with the rowcount for the target table and saved with a Date_time.
	The goal is to produce a history of fragmenation change over time on a per table basis.

	Ralph Clark - Plantware,Inc 5/2/02
**/

Set NoCount ON

Declare @rows int,
	@start int,
	@end int,
	@ps int,
	@es int,
	@esw int,
	@ape real,
	@sd real,
	@rat varchar(10),
	@lsf real,
	@esf real,
	@bfp real,
	@apd real,
	@str nvarchar(500)

--get rows in target table
Set @str = 'SELECT @rows = rows FROM ' + @db + '.dbo.sysindexes WHERE id = OBJECT_ID(' + CHAR(39) + @db +'.dbo.' + @table + Char(39) + ') AND indid < 2'

Exec sp_ExecuteSql @str, N'@rows Int Out', @rows Out

--get fragmenation details
CREATE TABLE #cnt (Record_id Int IDENTITY (1, 1),  info VARCHAR(100))

DECLARE @tbl VARCHAR(15), @parm VARCHAR(255)

SELECT @tbl = CONVERT(VARCHAR(50), OBJECT_ID((@db + '..'+ @table)))

SELECT @parm = 'OSQL /E /Q"DBCC SHOWCONTIG(' + @tbl + ')" /d ' + @db

INSERT #cnt EXEC master..xp_cmdshell @parm

select @start = CHARINDEX(': ', info), @end =  Len(info) from #cnt where record_id = 4

select @ps = Convert(real,Ltrim(Rtrim(Substring(Info, (@start+2), (@end - (@start+1)))))) from #cnt where record_id = 4

select @start = CHARINDEX(': ', info), @end =  Len(info) from #cnt where record_id = 5

select @es = Convert(real,Ltrim(Rtrim(Substring(Info, (@start+2), (@end - (@start+1)))))) from #cnt where record_id = 5

select @start = CHARINDEX(': ', info), @end =  Len(info) from #cnt where record_id = 6

select @esw = Convert(real,Ltrim(Rtrim(Substring(Info, (@start+2), (@end - (@start+1)))))) from #cnt where record_id = 6

select @start = CHARINDEX(': ', info), @end =  Len(info) from #cnt where record_id = 7

select @ape = Convert(real,Ltrim(Rtrim(Substring(Info, (@start+2), (@end - (@start+1)))))) from #cnt where record_id = 7

select @start = CHARINDEX(': ', info), @end =  CHARINDEX('%', info) from #cnt where record_id = 8

select @sd = Convert(real,Ltrim(Rtrim(Substring(Info, (@start+1), (@end - (@start+1)))))) from #cnt where record_id = 8

select @start = CHARINDEX('% [', info), @end =  CHARINDEX(']', info, @start) from #cnt where record_id = 8

select @rat = Ltrim(Rtrim(Substring(Info, (@start+1), (@end - (@start))))) from #cnt where record_id = 8

select @start = CHARINDEX(': ', info), @end =  CHARINDEX('%', info) from #cnt where record_id = 9

select @lsf = Convert(real,Ltrim(Rtrim(Substring(Info, (@start+1), (@end - (@start+1)))))) from #cnt where record_id = 9

select @start = CHARINDEX(': ', info), @end =  CHARINDEX('%', info) from #cnt where record_id = 10

select @esf = Convert(real,Ltrim(Rtrim(Substring(Info, (@start+1), (@end - (@start+1)))))) from #cnt where record_id = 10

select @start = CHARINDEX(': ', info), @end =  Len(info) from #cnt where record_id = 11

select @bfp = Convert(real,Ltrim(Rtrim(Substring(Info, (@start+2), (@end - (@start+1)))))) from #cnt where record_id = 11

select @start = CHARINDEX(': ', info), @end =  CHARINDEX('%', info) from #cnt where record_id = 12

select @apd = Convert(real,Ltrim(Rtrim(Substring(Info, (@start+1), (@end - (@start+1)))))) from #cnt where record_id = 12

Drop table #cnt

--save results

Insert PlantQueDG.dbo.tb_FragTrack_log 
	(Date_time, 
	DBName,
	Tablename, 
	TableRows, 
	PagesScnd, 
	ExtentSws,
	ExtentsScnd,
	AvgPagesExt,
	ScanDensity,
	ExtRatio,
	LogicalScnFrag,
	ExtScanFrag,
	AvgByteFree,
	AvgPageDens)
values
	(getdate(),
	@db,
	@table,
	@rows,
	@ps,
	@es,
	@esw,
	@ape,
	@sd,
	@rat,
	@lsf,
	@esf,
	@bfp,
	@apd)

return
GO