BPSoftware.com

-- ##################################################
-- get a list of table indexes
-- ##################################################
 
SELECT 
	ao.object_id AS table_id
	,ao.name AS table_name
	,i.index_id
	,i.is_primary_key
	,i.name AS index_name
FROM
	-- sys.tables can be used to retrieve a list of tables of type 'U'
	sys.all_objects ao LEFT JOIN sys.indexes i 
		ON (ao.object_id = i.object_id)
WHERE 
	(ao.type = N'U')

-- ##################################################

-- set options
SET NOCOUNT ON;
SET ANSI_PADDING ON;
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;

-- declare table to store table names
-- in SQL 2005 you can use table variables
-- if you do not need or want to store the data you can store it in a table
-- variable instead of a temporary table
DECLARE @tablenames TABLE (
	TableName VARCHAR(255)
	);

-- populate table with tablenames
INSERT INTO @tablenames
	SELECT
		name
	FROM
	sys.objects
	-- additional filters can be set to narrow table selection 
	WHERE
		([type] = N'U');

-- Declare a cursor to loop through table
DECLARE tablenames INSENSITIVE SCROLL CURSOR
FOR
	SELECT
		TableName
	FROM
		@tablenames;

-- Open the cursor.
OPEN tablenames

-- declare variables
DECLARE @curtablename AS VARCHAR(255);
DECLARE @cmd AS VARCHAR(1024);

-- Loop through all the tables
WHILE (1=1)
	BEGIN
		FETCH NEXT FROM tablenames INTO @curtablename;
		IF @@fetch_status <> 0 BREAK;
		-- run the command
		SET @cmd = 'DBCC CHECKTABLE ([' + rtrim(@curtablename) + '])';
		EXEC (@cmd)
		PRINT @cmd + CHAR(10) + CHAR(13);
	END

-- Close cursor
CLOSE tablenames;
DEALLOCATE tablenames;

-- ########################################
-- Create table to hold index information
SET NOCOUNT ON;

CREATE TABLE _IndexInformation (
	database_id SMALLINT
	,ObjectName varchar(255)
	,[object_id] int
	,IndexName varchar(255)
	,[index_id] int
	,index_level int
	,index_type_desc varchar(60)
	,avg_fragmentation_in_percent float
	,fragment_count float
	,page_count bigint
	,record_count bigint
	,index_depth int
);
-- ########################################

-- ########################################
-- clear table contents
TRUNCATE TABLE _IndexInformation;

-- populate table
INSERT INTO _IndexInformation
	SELECT
		database_id
		,object_name(i.object_id) AS [ObjectName]
		,i.[object_id]
		,i.name AS [IndexName]
		,dm.index_id
		,dm.index_level
		,dm.index_type_desc
		,dm.avg_fragmentation_in_percent
		,dm.fragment_count
		,dm.page_count
		,dm.record_count
		,dm.index_depth
	FROM
	-- database, table and index information can be specified
	/*
	sys.dm_db_index_physical_stats (
		{ database_id NULL 0 DEFAULT }
		,{ object_id NULL 0 DEFAULT }
		,{ index_id NULL 0 -1 DEFAULT }
		,{ partition_number NULL 0 DEFAULT }
		,{ mode NULL DEFAULT }
	)
	This example uses database 1; you should replace this with the ID of the database you wish to process
	*/
	sys.dm_db_index_physical_stats(1,null,null,null,'detailed') dm INNER JOIN sys.indexes i 
		ON (i.object_id = dm.object_id) AND (i.index_id = dm.index_id);
-- ########################################


-- ########################################
-- read index table and maintain indexes
SET NOCOUNT ON;
SET ANSI_PADDING ON;
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;


-- Declare variables.
DECLARE @tablename AS varchar(255)
DECLARE @indexname AS varchar(255)
DECLARE @frag AS float
DECLARE @cmd AS varchar(1024)

-- Declare a cursor.
DECLARE indexes INSENSITIVE SCROLL CURSOR
	FOR
		SELECT
			ObjectName,
			IndexName,
			avg_fragmentation_in_percent
		FROM
			_IndexInformation
		WHERE
			(avg_fragmentation_in_percent >= 5);

-- Open the cursor.
OPEN indexes

-- Loop through all the indexes.
WHILE (1=1)
	BEGIN
		FETCH NEXT FROM indexes INTO @tablename, @indexname, @frag;
		IF @@fetch_status <> 0 BREAK;
		-- if fragmentation is above 30% rebuild index
		-- if fragmentation is above 5% reorganize index
		IF (@frag >= 30)
			BEGIN
				SET @cmd = 'ALTER INDEX [' + rtrim(@indexname) + '] ON [' + @tablename +'] REBUILD;'
				EXEC (@cmd)
				PRINT @cmd;
			END
		ELSE IF (@frag >= 5)
			BEGIN
				SET @cmd = 'ALTER INDEX [' + rtrim(@indexname) + '] ON [' + @tablename +'] REORGANIZE;'
				EXEC (@cmd)
				PRINT @cmd;
			END;
		END

-- Close cursor
CLOSE indexes;
DEALLOCATE indexes;
-- ########################################


-- ########################################
-- delete index table
DROP TABLE _IndexInformation;
-- ########################################