-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-- Written by Kimberly L. Tripp, SQLskills.com
-- All Rights Reserved.
-- 
-- For more scripts, sample code and additional training resources,
-- go to http://www.SQLskills.com
--	Immerse yourself in SQL Server
--
-- Disclaimer - Thoroughly test this script, execute at your own risk.
-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

-- Problem - You want to DEFRAG an index for the first time... You didn't
-- set the fillfactor on the index create and you've never rebuilt the 
-- index. In this case the OrigFillFactor (in sysindexes) is likely to have
-- the server's default - which is likely to be 0)... Since DBCC INDEXDEFRAG 
-- uses the OrigFillFactor to rebuild you probably want to change this value
-- SO - if you want something other than 0 then you'd need to force a FULL 
-- rebuild (and therefore downtime) of the table to change the fillfactor... 
-- Unlike DBCC DBREINDEX the DBCC INDEXDEFRAG command doesn't have a parameter
-- to set the FILLFACTOR setting.....BUMMER. 

-- Enter this script...we need a hack for SQL Server 2000.
-- The change that's needed is to a system table called sysindexes and rather
-- than force you to try and do this with the potential to "whack" all of your 
-- rows I've created a user-defined system procedure to be placed in master.

-- The benefit of creating a stored procedure while system updates are on is that
-- the procedure retains those rights even after they are turned off. However,
-- ONLY system administrators can execute this script and create a procedure like
-- this.

-- USE AT YOUR OWN RISK!!!!! Please test FIRST!!! And - if you find any 
-- issues/errors, please send email to Kimberly@SQLskills.com.

USE master
go

EXEC sp_configure 'allow updates', 1
go

RECONFIGURE WITH OVERRIDE
go

IF OBJECTPROPERTY(object_id('sp_UpdateSysindexesOrigFillFactor'), 'IsProcedure') = 1
	DROP PROCEDURE dbo.sp_UpdateSysindexesOrigFillFactor
go

CREATE PROCEDURE dbo.sp_UpdateSysindexesOrigFillFactor
(
	@TableName	sysname = NULL,
	@IndexName	sysname = NULL,
	@FillFactor	tinyint = NULL
)
AS
SET NOCOUNT ON
-- Written by Kimberly L. Tripp of SYSolutions, Inc.
-- For more code samples go to http://www.sqlskills.com

IF @TableName IS NULL OR @IndexName IS NULL OR @FillFactor IS NULL
	BEGIN
		RAISERROR('For procedure:sp_UpdateSysindexesOrigFillFactor all parameters are required. Please re-execute specifying all three parameters (@TableName, @IndexName, @FillFactor). For example, EXEC dbo.sp_UpdateSysindexesOrigFillFactor @TableName = ''charge'', @IndexName = ''charge_ident'', @FillFactor = 90. This procedure only allows ONE table/ONE Index at a time!', 16, 1)
		RETURN
	END

IF @TableName IS NOT NULL
	BEGIN
		IF (OBJECTPROPERTY(object_id(@TableName), 'IsUserTable') IS NULL
				AND OBJECTPROPERTY(object_id(@TableName), 'IsView') IS NULL) 
			BEGIN
				RAISERROR('Object: %s does not exist. Please verify the objectname and re-execute. For example, EXEC dbo.sp_UpdateSysindexesOrigFillFactor @TableName = ''charge'', @IndexName = ''charge_ident'', @FillFactor = 90. This procedure only allows ONE table/ONE Index at a time!', 16, 1, @TableName)
				RETURN
			END

		IF (OBJECTPROPERTY(object_id(@TableName), 'IsUserTable') = 0 
				AND OBJECTPROPERTY(object_id(@TableName), 'IsView') = 0) 
			BEGIN
				RAISERROR('Object: %s exists but is NOT a User-defined Table or Indexed View. This procedure only accepts valid table names to process for index rebuilds.', 16, 1, @TableName)
				RETURN
			END

		IF (OBJECTPROPERTY(object_id(@TableName), 'IsIndexed') = 0)
			BEGIN
				RAISERROR('Object: %s does not have any indexes.', 16, 1, @TableName)
				RETURN
			END
    END

IF @IndexName IS NOT NULL
	BEGIN
		IF INDEXPROPERTY(object_id(@TableName), @IndexName, 'IndexID') IS NULL
			BEGIN
				RAISERROR('Index: %s does not exist or does not belong to this table: %s. This procedure only accepts valid table names and their associated indexes to be specified and only allows ONE table/ONE Index at a time!', 16, 1, @IndexName, @TableName)
				RETURN
			END
    END

IF @FillFactor NOT BETWEEN 1 AND 100
	BEGIN
		RAISERROR('FillFactor: %d is not valid. FillFactor must be a value between 1 and 100.', 16, 1, @FillFactor)
		RETURN
    END


-- Verify before and after FillFactor values:
SELECT OrigFillFactor AS [Original FillFactor]
FROM dbo.sysindexes 	
WHERE id = object_id(@TableName) AND [name] = @IndexName

UPDATE dbo.sysindexes
	SET OrigFillFactor = @FillFactor
	WHERE id = object_id(@TableName) AND [name] = @IndexName

SELECT OrigFillFactor AS [Current FillFactor Value]
FROM dbo.sysindexes 	
WHERE id = object_id(@TableName) AND [name] = @IndexName
go

EXEC sp_configure 'allow updates', 0
go

RECONFIGURE WITH OVERRIDE
go

---------------------------------------------------------
-- This next section was used SOLELY for testing the 
-- proc and it's impact on DBCC INDEXDEFRAG
-- NOTE: INDEXDEFRAG does not move data where there's
-- no fragmentation.
---------------------------------------------------------

-- USE Credit
-- go
-- 
-- DBCC SHOWCONTIG(N'member2') --WITH ALL_INDEXES
-- go
-- 
-- EXEC sp_UpdateSysindexesOrigFillFactor N'Member2', N'Member2PK', 93
-- go
-- 
-- DBCC INDEXDEFRAG('credit', 'Member2', 'Member2PK')
-- go
-- 
-- DBCC SHOWCONTIG(N'member2') --WITH ALL_INDEXES
-- go
-- 
-- -- To view the values in sysindexes
-- SELECT CASE
-- 		WHEN indid = 0 THEN 'Heap'
-- 		WHEN indid = 1 THEN 'CL'
-- 		ELSE 'NC ' + convert(varchar(5), indid)
-- 	END AS IndexID, 
-- 	object_name(id) AS ObjectName,
-- 	name AS IndexName,
-- 	OrigFillFactor AS 'FillFactor'
-- FROM sysindexes
--     WHERE indid <= 250
-- ORDER BY 2,1
-- go
