SQL2008R2 Traversal of all table update statistics and Index rebuilds

"2. The following is the update STATISTICS"

DECLARE updatestatisticstables CURSOR read_only      for SELECT sst.name,             schema_name (sst.schema_id)      from   Sys.tables SST      WHERE  SST. TYPE = ' U '    DECLARE @name   varchar,            @schema varchar         OPEN updatestatisticstables         FETCH NEXT from Updatestatisticstables to @name, @schema while         (@ @FETCH_STATUS <>-1)      BEGIN          IF (@ @FETC H_status <>-2)            BEGIN                    DECLARE @sql NVARCHAR (1024x768)            SET @sql = ' UPDATE STATISTICS ' + Quotename (@ Schema)                               +                               '. ' + Quotename (@name)                      EXEC sp_executesql @sql            END               FETCH NEXT from Updatestatisticstables into @name, @schema      END         CLOSE updatestatisticstables         deallocate Updatestatisticstables         GO    

Update STATISTICS Tblcompany--  table name use Tblcompany;--database name exec sp_updatestats--Updates all table statistics

-------------------------------------------------

"1. The following are index rebuilds"

Excerpt from: http://www.cnblogs.com/shijun/archive/2012/04/21/2461737.html

The project upgrade database was upgraded from SQL2000 to 2008R2, and today the database tables were scanned for fragmentation, and some table fragments were found to be large, so they decided to rebuild the index, and the online help was the best teacher, and the relevant script excerpt was checked.

Reference sys.dm_db_index_physical_stats

SELECT    object_name (object_id) as objectname,    object_id as Objectid,    index_id as IndexID,    Partition_ Number as Partitionnum,    avg_fragmentation_in_percent as Frafrom sys.dm_db_index_physical_stats (DB_ID (), NULL, NULL, NULL, ' LIMITED ') WHERE avg_fragmentation_in_percent > 10.0 and index_id > 0;

Rebuilding or re-organizing an index using sys.dm_db_index_physical_stats in a script ( from online help )

SET NOCOUNT on;declare @objectid int;declare @indexid int;declare @partitioncount bigint;declare @schemaname nvarchar ( 130); DECLARE @objectname nvarchar (130); DECLARE @indexname nvarchar (130); DECLARE @partitionnum bigint;declare @partitions bigint;declare @frag float;declare @command nvarchar (4000); --Conditionally Select tables and indexes from the Sys.dm_db_index_physical_stats function--and convert object and Inde x IDs to names. SELECT object_id as Objectid, index_id as IndexID, partition_number as Partitionnum, AVG_FRAGMENTATION_IN_PERC ent as Fraginto #work_to_doFROM sys.dm_db_index_physical_stats (db_id (), NULL, NULL, NULL, ' LIMITED ') WHERE Avg_fragmenta Tion_in_percent > 10.0 and index_id > 0; --Declare the cursor for the list of partitions to be processed. DECLARE partitions CURSOR for SELECT * from #work_to_do; -Open the cursor. OPEN partitions; --Loop through the partitions.        while (1=1) BEGIN; FETCH NEXT from partitions to @Objectid, @indexid, @partitionnum, @frag;        IF @ @FETCH_STATUS < 0 break; SELECT @objectname = QUOTENAME (o.name), @schemaname = QUOTENAME (s.name) from sys.objects as O JOIN sys.schem        As as s on s.schema_id = o.schema_id WHERE o.object_id = @objectid;        SELECT @indexname = QUOTENAME (name) from sys.indexes WHERE object_id = @objectid and index_id = @indexid; SELECT @partitioncount = count (*) from sys.partitions WHERE object_id = @objectid and index_id = @in Dexid;        --a arbitrary decision point at which to switch between reorganizing and rebuilding. IF @frag < 30.0 SET @command = n ' ALTER INDEX ' + @indexname + n ' on ' + @schemaname + n '. ' + @objectname + N        ' REORGANIZE '; IF @frag >= 30.0 SET @command = n ' ALTER INDEX ' + @indexname + n ' on ' + @schemaname + n '. ' + @objectname +        N ' REBUILD '; IF @partitioncount > 1 SET @command = @command + N ' PartitIon= ' + CAST (@partitionnum as nvarchar (10));        EXEC (@command);    PRINT N ' Executed: ' + @command; END; --Close and deallocate the cursor. CLOSE partitions;deallocate partitions; -Drop the temporary table. DROP TABLE #work_to_do; GO

SQL2008R2 Traversal of all table update statistics and Index rebuilds