/*Perform a 'USE <database name>' to select the database in which to run the script.*/

-- Create the table.
IF OBJECT_ID('tempdb..##fraglist') IS NOT NULL
DROP table ##fraglist;

 IF OBJECT_ID('tempdb..##fragstatus') IS NOT NULL
DROP table ##fragstatus; 

CREATE TABLE ##fraglist (
	object_id varchar(100),
	index_id varchar(50),
	table_name varchar(100),
	index_name varchar(100),
	avg_fragmentation_in_percent decimal
)

 CREATE TABLE ##fragstatus (
	table_name varchar(100),
	index_name varchar(100)
) 

-- Declare variables

SET NOCOUNT ON;
DECLARE @tablename varchar(255);
DECLARE @execstr   varchar(400);
DECLARE @objectid  int;
DECLARE @indexid   int;
DECLARE @frag      decimal;
DECLARE @maxfrag   decimal;
DECLARE @minfrag decimal; 

DECLARE @indexname varchar(255);



-- Decide on the maximum and minimum fragmentation to allow for rebuild or reorg. fragmentation below minfrag will be ignored
SELECT @maxfrag = 30.0;
SELECT @minfrag = 5.0;

-- Declare a cursor.
DECLARE tables CURSOR FOR
   SELECT TABLE_SCHEMA + '.' + TABLE_NAME
   FROM INFORMATION_SCHEMA.TABLES
   WHERE TABLE_TYPE = 'BASE TABLE';

-- Open the cursor.
OPEN tables;

-- Loop through all the tables in the database.
FETCH NEXT
   FROM tables
   INTO @tablename;

WHILE @@FETCH_STATUS = 0
BEGIN;

DECLARE indexes CURSOR for
SELECT name,index_id FROM sys.indexes WHERE object_id = OBJECT_ID(@tablename)

open indexes;

FETCH NEXT from indexes into @indexname,@indexid
while @@FETCH_STATUS = 0
BEGIN
-- Do the showcontig of all indexes of the table
   INSERT INTO ##fraglist 
   select [object_id],index_id,@tablename as table_name,@indexname as index_name,avg_fragmentation_in_percent from sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(@tablename) ,@indexid,NULL,'LIMITED')

FETCH NEXT FROM indexes into @indexname,@indexid
END
close indexes;
deallocate indexes;

   FETCH NEXT
      FROM tables
      INTO @tablename;
END;

-- Close and deallocate the cursor.
CLOSE tables;
DEALLOCATE tables;

select * from ##fraglist

PRINT 'Started Defrag/rebuild...';

DECLARE fraglist cursor for select table_name,index_name, avg_fragmentation_in_percent from ##fraglist where index_name IS NOT NULL and avg_fragmentation_in_percent > @minfrag

open fraglist

FETCH NEXT FROM fraglist into @tablename, @indexname, @frag

WHILE @@FETCH_STATUS = 0
BEGIN

if @frag > @maxfrag
BEGIN
SELECT @execstr = 'ALTER INDEX [' + @indexname +'] ON [' + REPLACE(@tablename,'.','].[') + '] REBUILD WITH (FILLFACTOR = 80, 
			SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON)';
END;
ELSE
BEGIN
SELECT @execstr ='ALTER INDEX [' + @indexname + '] ON [' + REPLACE(@tablename,'.','].[') + '] REORGANIZE';
END;

exec (@execstr);
INSERT INTO ##fragstatus --USE THIS TABLE TO VIEW THE ACTIVITY PROGRESS
VALUES
(@tablename, @indexname);

FETCH NEXT FROM fraglist into @tablename, @indexname, @frag
END

close fraglist
deallocate fraglist

 select * from ##fragstatus


PRINT 'Defrag/rebuild completed';