Reindexing - Update Statistics - Based on Fragmentation Level

OBJECTIVE: Reindexing based on Fragmentation Level:

CREATE PROCEDURE [dbo].[P_REINDEXING_REBUILD_STATSUPDATE]
AS
BEGIN
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
SET ANSI_PADDING ON;
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[T_Fragmentation_Info]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].T_Fragmentation_Info(
[ID] int identity(1,1) NOT NULL,
[Frag_Date] [datetime] NULL,
[Table_Name] [varchar](100) NULL,
[Databasename] [varchar](100) NULL,
[Index_ID] [int] NULL,
[Index_Name] [varchar](100) NULL,
[Avg_Frag_Percentage] [numeric](15, 12) NULL,
[Action_Take] [varchar](30) NULL,
[Index_Type] [varchar](20) NULL,
[INDEXSTATUS] INT NOT NULL DEFAULT(1),
[STATSSTATUS] INT NOT NULL DEFAULT(1),
[INDEXQUERY] NVARCHAR(4000) NULL,
[STATSQUERY] NVARCHAR(4000) NULL,
[IX_START_DATE] DATETIME NULL,
[IX_END_DATE] DATETIME NULL,
[STATS_START_DATE] DATETIME NULL,
[STATS_END_DATE] DATETIME NULL
) ON [PRIMARY]
END
SET ANSI_PADDING OFF;

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].T_objectsinfo') AND type in (N'U'))
BEGIN
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
SET ANSI_PADDING ON;
create table [dbo].T_objectsinfo (ServerName VARCHAR(100), DatabaseName VARCHAR(100),Name VARCHAR(100), ID VARCHAR(100))
END
SET ANSI_PADDING OFF;
--Select TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME,* from INFORMATION_SCHEMA.TABLES Where table_type='Base Table'


Truncate table T_objectsinfo
Truncate table [DBMONITOR].[dbo].T_Fragmentation_Info

DECLARE @TableInfo TABLE ( ServerName VARCHAR(100), DatabaseName VARCHAR(100),Name VARCHAR(100), ID VARCHAR(100))
DECLARE @command VARCHAR(5000)
SELECT @command = 'Use [' + '?' + '] Select @@servername as ServerName, ' + '''' + '?' + '''' + ' AS DatabaseName,Name,ID from sys.sysobjects (nolock) where xtype=''u'' and (name NOT LIKE ''%TEMP%'' AND NAME NOT LIKE ''%DELETE%'' AND
NAME NOT LIKE ''%TEST%'' AND name NOT LIKE ''%2010%'' AND name NOT LIKE ''%2009%'' AND name NOT LIKE ''%2008%''
AND name NOT LIKE ''%2007%'' )'
--Select @command
INSERT INTO T_objectsinfo (ServerName, DatabaseName,Name, ID)
EXEC sp_MSForEachDB @command SELECT ServerName, DatabaseName,Name,ID from @TableInfo
select * from T_objectsinfo


SET nocount ON
DECLARE @strDatabaseName Varchar (50)
Declare @strName Varchar (200)
Declare @intID int
Declare curTables Cursor For Select DatabaseName,Name,ID from T_objectsinfo (nolock)
Open curTables
Fetch Next From curTables Into @strDatabaseName,@strName,@intID
While @@FETCH_STATUS = 0
Begin


Declare @SQL nVarchar (4000)
BEGIN
SET @SQL='
USE ' + @strDatabaseName + '
INSERT INTO [DBMONITOR].[dbo].T_Fragmentation_Info
([Databasename],[Frag_Date],[Table_Name],[Index_ID],[Index_Name],[Avg_Frag_Percentage])
SELECT '''+@strDatabaseName+''' AS [Databasename] ,Getdate() as [Frag_Date],'''+ @strName+''' as [Table_Name],a.index_id as [Index_ID], name as [Index_Name] , round(avg_fragmentation_in_percent,2) as [Avg_Frag_Percentage]
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('''+ @strName+'''),NULL, NULL, NULL) AS a JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id;
'
PRINT @SQL
EXEC (@SQL)

update [DBMONITOR].[dbo].T_Fragmentation_Info set Action_Take='REORGANIZE' where Avg_Frag_Percentage between 5 and 30 and Action_Take is null and databasename=@strDatabaseName
update [DBMONITOR].[dbo].T_Fragmentation_Info set Action_Take='REBUILD' where Avg_Frag_Percentage>30 and Action_Take is null and databasename=@strDatabaseName
update [DBMONITOR].[dbo].T_Fragmentation_Info set Action_Take='None' where Avg_Frag_Percentage<5 databasename="@strDatabaseName" index_type="'CLUSTERED'" index_id="1" databasename="@strDatabaseName" index_type="'NONCLUSTERED'">1 and Index_Type is null and databasename=@strDatabaseName

End

Fetch Next From curTables Into @strDatabaseName,@strName,@intID
End
Close curTables
Deallocate curTables
SET nocount OFF

Declare @strID Varchar (20)
Declare @strAction Varchar (20)
Declare @strTableName Varchar (100)
Declare @strIndexName Varchar (100)
Declare @strQuery Varchar (1000)
Declare curTablesIndexs Cursor For Select ID,[Databasename],Table_Name,Index_Name,ACTION_TAKE from [DBMONITOR].[dbo].T_Fragmentation_Info WHERE (INDEXSTATUS=1 OR STATSSTATUS=1)
Open curTablesIndexs
Fetch Next From curTablesIndexs Into @strID,@strDatabaseName,@strTableName,@strIndexName,@strAction
While @@FETCH_STATUS = 0
Begin
IF @STRACTION='None'
BEGIN
PRINT 'NO REINDEX OR REORGANIZE NECESSARY'
UPDATE T_Fragmentation_Info SET INDEXSTATUS=0,STATSSTATUS=0,IX_START_DATE=GETDATE(),IX_END_DATE=GETDATE(),STATS_START_DATE=GETDATE(),STATS_END_DATE=GETDATE(),INDEXQUERY='NO ACTION NECESSARY',STATSQUERY='NO ACTION NECESSARY' WHERE ID=@STRID
END
ELSE
BEGIN
UPDATE T_Fragmentation_Info SET IX_START_DATE=GETDATE() WHERE ID=@STRID
SET @STRQUERY='USE ' + @strDatabaseName + '
ALTER INDEX ' + @strIndexName + ' on ' + @strTableName + ' '+@strAction+';
'
PRINT (@STRQUERY )

UPDATE T_Fragmentation_Info SET INDEXSTATUS=0,IX_END_DATE=GETDATE(),STATS_START_DATE=GETDATE(),INDEXQUERY=@STRQUERY WHERE ID=@STRID
SET @STRQUERY='USE ' + @strDatabaseName + '
UPDATE STATISTICS ' + @strTableName +' '+ @strIndexName + ';
'
PRINT (@STRQUERY )
UPDATE T_Fragmentation_Info SET STATSSTATUS=0,STATS_END_DATE=GETDATE(),STATSQUERY=@STRQUERY WHERE ID=@STRID

END

Fetch Next From curTablesIndexs Into @strID,@strDatabaseName,@strTableName,@strIndexName,@strAction
End
Close curTablesIndexs
Deallocate curTablesIndexs

END