BEGIN
SET NOCOUNT ON
SET XACT_ABORT ON

-------------------------- Defrag completely (Y/N) --------------
DECLARE @Defragmode VARCHAR(1)
SET @Defragmode = 'Y'
-----------------------------------------------------------------

---------------------Number of Hours to Execute------------------
DECLARE @NUMOFHRS INT
SET @NUMOFHRS = 24
-----------------------------------------------------------------

-----Change database Recovery Mode to SIMPLE/FULL (Y/N) ---------
DECLARE @Recoverymode VARCHAR(1)
SET @Recoverymode = 'N'
-----------------------------------------------------------------

IF CURSOR_STATUS('global','crsr4')>=-1
        DEALLOCATE crsr4

DECLARE @max INT
DECLARE @min INT
DECLARE @lobcount INT
DECLARE @command VARCHAR(4000)
DECLARE @objectname VARCHAR(130)
DECLARE @indexname VARCHAR(130)
DECLARE @Edition NVARCHAR(100)
DECLARE @STARTTIME DATETIME
DECLARE    @DBName    nvarchar(50)
DECLARE    @SQLString nvarchar(500)
select @DBName =db_name()
IF @Recoverymode = 'Y'
BEGIN
	SET @SQLString =  'ALTER DATABASE [' + @DBName + '] ' + 'SET RECOVERY SIMPLE WITH NO_WAIT'
	EXECUTE( @SQLString )
END

SELECT  @Edition = CONVERT(NVARCHAR(100), SERVERPROPERTY ('edition'))
SELECT @STARTTIME = GETDATE()
DECLARE @FillValue INT
SET @FillValue = 70

IF OBJECT_ID('IndexRebuildList') IS NOT NULL
BEGIN
SELECT @max = (SELECT COUNT(1) FROM IndexRebuildList WHERE ENDTIME is null)
IF @max = 0  OR @Defragmode = 'Y'
BEGIN
DROP TABLE IndexRebuildList
END
END

IF OBJECT_ID('IndexRebuildList') IS NULL
BEGIN
CREATE TABLE IndexRebuildList (ID INT IDENTITY(1,1),IndexName varchar(50),TableName varchar(50),ENDTIME DATETIME)

CREATE CLUSTERED INDEX IX_CL_IndexList ON IndexRebuildList(ID)

INSERT IndexRebuildList (IndexName,TableName)
SELECT Name as IndexName,object_name(id) as TableName FROM SYSINDEXES as i
where NOT EXISTS(
SELECT 1 FROM SYSINDEXKEYS as P WHERE EXISTS (
SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS AS c JOIN
              INFORMATION_SCHEMA.TABLES AS t
              ON t.TABLE_NAME = c.TABLE_NAME
WHERE          COLUMNPROPERTY(OBJECT_ID(c.TABLE_NAME),c.COLUMN_NAME,'IsIdentity') = 1 AND
               C.DATA_TYPE NOT IN ('xml','image','text','ntext') AND
               t.TABLE_TYPE = 'Base Table' AND
               t.TABLE_NAME NOT LIKE 'dt%' AND
               t.TABLE_NAME NOT LIKE 'MS%' AND
               t.TABLE_NAME NOT LIKE 'syncobj_%' 
			   AND OBJECT_ID(t.TABLE_NAME)=P.ID
              AND c.ORDINAL_POSITION=P.COLID)
AND I.ID=P.ID AND I.INDID=P.indid)
AND EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES AS Bt              
WHERE          Bt.TABLE_TYPE = 'Base Table' AND
               Bt.TABLE_NAME NOT LIKE 'dt%' AND
               Bt.TABLE_NAME NOT LIKE 'MS%' AND
               Bt.TABLE_NAME NOT LIKE 'syncobj_%' AND 
			   OBJECT_ID(BT.TABLE_NAME)=I.ID)
AND EXISTS (select 1 from sysindexkeys k 
			where k.id=i.id and i.indid=k.indid)
END			
SELECT @max = (SELECT MAX(ID) FROM IndexRebuildList WHERE ENDTIME IS NULL)
SELECT @min = (SELECT MIN(ID) FROM IndexRebuildList WHERE ENDTIME IS NULL)

WHILE @min <= @max AND GETDATE() < DATEADD(HOUR,@NUMOFHRS,@STARTTIME)
BEGIN

   SELECT  @objectname = tablename, @indexname  = indexname
   FROM    IndexRebuildList
   WHERE   ID = @min

    SELECT @lobcount = COUNT(1) FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
        JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu ON tc.CONSTRAINT_NAME = ccu.Constraint_name
		JOIN INFORMATION_SCHEMA.COLUMNS c ON tc.TABLE_NAME = c.TABLE_NAME
    WHERE tc.CONSTRAINT_TYPE = 'Primary Key'
	AND C.DATA_TYPE IN ('xml','image','text','ntext')
	AND TC.TABLE_NAME=@objectname
	AND TC.CONSTRAINT_NAME=@IndexName

   IF  @Edition = 'Enterprise Edition (64-bit)' AND @lobcount = 0
   BEGIN
       SET @command = 'ALTER INDEX ' + @indexname + ' ON ' + @objectname +' REBUILD WITH(ONLINE = ON, SORT_IN_TEMPDB = ON, FILLFACTOR = '+CAST(@FillValue AS VARCHAR(10))+')';
   END

   IF @Edition <> 'Enterprise Edition (64-bit)' OR @lobcount > 0
   BEGIN
       SET @command = 'ALTER INDEX ' + @indexname + ' ON ' + @objectname +' REBUILD WITH(ONLINE = OFF, SORT_IN_TEMPDB = ON, FILLFACTOR = '+CAST(@FillValue AS VARCHAR(10))+')';
   END 
 

   BEGIN
       EXEC (@command)
   END
   UPDATE IndexRebuildList
   SET ENDTIME=GETDATE()
   WHERE ID=@MIN
   SET @min = @min+1

END

BEGIN 
	DECLARE crsr4 INSENSITIVE CURSOR FOR select  'UPDATE STATISTICS '+rtrim(TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES where TABLE_TYPE='BASE TABLE'
	open crsr4
	fetch next FROM crsr4 into @SQLString
	while (@@FETCH_STATUS = 0)
	begin		
		EXECUTE sp_executesql @SQLString		
		fetch next FROM crsr4 into @SQLString
	end
	close crsr4
	deallocate crsr4
END

IF @Recoverymode = 'Y'
BEGIN
	SET @SQLString =  'ALTER DATABASE [' + @DBName + '] ' + 'SET RECOVERY FULL WITH NO_WAIT'
	EXECUTE( @SQLString )
END
END
GO