Automated Job reindex, update stats - All Tables

These scripts accomplish reindexing/defraging your database and updating the sql statistics. It also allows you to stop and restart the execution of the reindex or update statistics at any time, and when you start the script again, it starts where it left off.
This script also takes your table fragmentation to 0, which is done by doing a reindex of the primary key indexes.

There are 3 scripts you need in order for this to work. The first script creates a table (Index_Log) that stores live and historical data about which tables and indexes have been reindexed or had update statistics run. The second script goes through all of your base tables and does a dbreindex on each index. The third script goes through all of your base tables and does an update statistics for each index.

You can also use the Index_Log table which stores the progress information for each job to view real time stats on the status of each job. I personally, made a php script that displays the progress of the jobs, which does nothing but display the information from the table based on Job Type (Indexing or Update Stats). It also allows me to view the total execution time and a history of every job. What you do with the table for reporting and viewing purposes is up to you, but you need it to run the jobs.

INSTRUCTIONS: Execute the first script in query analyzer to create the table. Next, Create a job under maintanence, sql agent, Jobs. Cut and paste the second script as step 1 of the job and save the job. Next, create another job for the third script and add it as step 1, and save the job. Now all you have to do is run your jobs whenever you need to, and view the Index_Log table to see the status of the jobs.


-#############EXECUTE THIS SCRIPT ONE TIME#############-
--------------------CREATE INDEX_LOG TABLE------------------
CREATE TABLE [dbo].[Index_Log] (
[pkIndex_Log] [int] IDENTITY (1, 1) NOT NULL ,
[Complete] [bit] NULL ,
[Table_Name] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Index_Name] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Start_Date] [datetime] NULL ,
[End_Date] [datetime] NULL ,
[Run_No] [int] NULL ,
[Run_Type] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
--------------------END CREATE INDEX_LOG TABLE------------------


-#############SAVE THIS AS STEP 1 FOR JOB 1#############-
------------------INDEXING JOB - DBREINDEX---------------------------
----------------------------------Insert table values------------------
--count records in Index_Log where Complete=0
if (Select count(pkIndex_Log) as Ind_Count from Index_Log where Complete=0 and Run_Type='Indexing') = 0
Begin
-----START INSERT-----
DECLARE @Cur_Run int
SET @Cur_Run=(select isnull(max(Run_No),0) + 1 from Index_Log)

DECLARE @TableName sysname
DECLARE cur_reindex CURSOR FOR

SELECT table_name
FROM information_schema.tables
OPEN cur_reindex
FETCH NEXT FROM cur_reindex INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @IndexName sysname
DECLARE cur_reindexA CURSOR FOR
SELECT i.name as index_name
FROM dbo.sysindexes i
WHERE id = object_id(@TableName) and i.indid > 0 and i.indid < 255
and (indexkey_property(object_id(@TableName), i.indid, 1, N'isdescending') is not null)
and (i.name is not null) and dpages>0
OPEN cur_reindexA
FETCH NEXT FROM cur_reindexA INTO @IndexName
WHILE @@FETCH_STATUS = 0
BEGIN

insert into Index_Log (Complete, Table_Name, Index_Name, Run_No) values(0, @TableName, @IndexName, @Cur_Run)

FETCH NEXT FROM cur_reindexA INTO @IndexName
END
CLOSE cur_reindexA
DEALLOCATE cur_reindexA
FETCH NEXT FROM cur_reindex INTO @TableName
END
CLOSE cur_reindex
DEALLOCATE cur_reindex
-----END INSERT-----
End
----------------------------------END Insert table values------------------
DECLARE @doTableName varchar(75), @doIndexName varchar(75), @dopkIndex_Log int
DECLARE cur_doindex CURSOR FOR
select Table_Name, Index_Name, pkIndex_Log from Index_Log where Complete=0 and Run_Type='Indexing'
OPEN cur_doindex
FETCH NEXT FROM cur_doindex INTO @doTableName, @doIndexName, @dopkIndex_Log
WHILE @@FETCH_STATUS = 0
BEGIN
update Index_Log set Start_Date=getDate() where pkIndex_Log=@dopkIndex_Log
DBCC DBREINDEX (@doTableName, @doIndexName, 80, sorted_data_reorg)
update Index_Log set End_Date=getDate(), Complete=1 where pkIndex_Log=@dopkIndex_Log
FETCH NEXT FROM cur_doindex INTO @doTableName, @doIndexName, @dopkIndex_Log
END
CLOSE cur_doindex
DEALLOCATE cur_doindex


-#############SAVE THIS AS STEP 1 FOR JOB 2#############-
----------------------------------Insert table values------------------
--count records in Index_Log where Complete=0
if (Select count(pkIndex_Log) as Ind_Count from Index_Log where Complete=0 and Run_Type='Up-Stats') = 0
Begin
-----START INSERT-----
DECLARE @Cur_Run int
SET @Cur_Run=(select isnull(max(Run_No),0) + 1 from Index_Log)

DECLARE @TableName sysname
DECLARE cur_reindex CURSOR FOR

SELECT table_name
FROM information_schema.tables
OPEN cur_reindex
FETCH NEXT FROM cur_reindex INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @IndexName sysname
DECLARE cur_reindexA CURSOR FOR
SELECT i.name as index_name
FROM dbo.sysindexes i
WHERE id = object_id(@TableName) and i.indid > 0 and i.indid < 255
and (indexkey_property(object_id(@TableName), i.indid, 1, N'isdescending') is not null)
and (i.name is not null) and dpages>0
OPEN cur_reindexA
FETCH NEXT FROM cur_reindexA INTO @IndexName
WHILE @@FETCH_STATUS = 0
BEGIN

insert into Index_Log (Run_Type, Complete, Table_Name, Index_Name, Run_No) values('Up-Stats', 0, @TableName, @IndexName, @Cur_Run)

FETCH NEXT FROM cur_reindexA INTO @IndexName
END
CLOSE cur_reindexA
DEALLOCATE cur_reindexA
FETCH NEXT FROM cur_reindex INTO @TableName
END
CLOSE cur_reindex
DEALLOCATE cur_reindex
-----END INSERT-----
End
----------------------------------END Insert table values------------------

DECLARE @doTableName varchar(75), @doIndexName varchar(75), @dopkIndex_Log int
DECLARE cur_doindex CURSOR FOR
select Table_Name, Index_Name, pkIndex_Log from Index_Log where Complete=0 and Run_Type='Up-Stats'
OPEN cur_doindex
FETCH NEXT FROM cur_doindex INTO @doTableName, @doIndexName, @dopkIndex_Log
WHILE @@FETCH_STATUS = 0
BEGIN
update Index_Log set Start_Date=getDate() where pkIndex_Log=@dopkIndex_Log
exec ('update Statistics ' + @doTableName + ' ' + @doIndexName + ' WITH FULLSCAN')
update Index_Log set End_Date=getDate(), Complete=1 where pkIndex_Log=@dopkIndex_Log
FETCH NEXT FROM cur_doindex INTO @doTableName, @doIndexName, @dopkIndex_Log
END
CLOSE cur_doindex
DEALLOCATE cur_doindex