SQL2008R2 Traversal of all table update statistics and Index rebuilds

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

"2. The following is the update STATISTICS"

 DECLAREUpdatestatisticstablesCURSORRead_Only for      SELECTSst.name, Schema_name (sst.schema_id) fromSys.tables SSTWHERESst. TYPE= 'U'    DECLARE @name   VARCHAR( the),            @schema VARCHAR( +)         OPENUpdatestatisticstablesFETCH NEXT  fromUpdatestatisticstables into @name,@schema          while(@ @FETCH_STATUS <> -1 )      BEGIN          IF(@ @FETCH_STATUS <> -2 )            BEGIN                    DECLARE @sql NVARCHAR(1024x768)            SET @sql='UPDATE STATISTICS' + Quotename(@schema)                               +                               '.' + Quotename(@name)                      EXECsp_executesql@sql            END               FETCH NEXT  fromUpdatestatisticstables into @name,@schema      END         CLOSEUpdatestatisticstablesdeallocateUpdatestatisticstablesGO    

UPDATE STATISTICS Tblcompany--  table name use   tblcompany;--database name EXEC sp_updatestats-- Update 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) asObjectName,object_id  asObjectid, index_id asIndexID, Partition_number asPartitionnum, Avg_fragmentation_in_percent asfra fromSys.dm_db_index_physical_stats (db_id(),NULL,NULL,NULL,'LIMITED')WHEREAvg_fragmentation_in_percent> 10.0  andindex_id> 0;

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

SETNOCOUNT on;DECLARE @objectid int;DECLARE @indexid int;DECLARE @partitioncount bigint;DECLARE @schemaname nvarchar( the); DECLARE @objectname nvarchar( the); DECLARE @indexname nvarchar( the); 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 index IDs to names.SELECT    object_id  asObjectid, index_id asIndexID, Partition_number asPartitionnum, Avg_fragmentation_in_percent asFrag into#work_to_do fromSys.dm_db_index_physical_stats (db_id(),NULL,NULL,NULL,'LIMITED')WHEREAvg_fragmentation_in_percent> 10.0  andindex_id> 0; --Declare the cursor for the list of partitions to be processed.DECLAREPartitionsCURSOR  for SELECT *  from#work_to_do;--Open the cursor.OPENpartitions;--Loop through the partitions. while(1=1)    BEGIN; FETCH NEXT            fromPartitions into @objectid,@indexid,@partitionnum,@frag; IF @ @FETCH_STATUS < 0  Break; SELECT @objectname = QUOTENAME(O.name),@schemaname = QUOTENAME(s.name) fromSys.objects asoJOINSys.schemas asS ons.schema_id=o.schema_idWHEREO.object_id = @objectid; SELECT @indexname = QUOTENAME(name) fromsys.indexesWHERE  object_id = @objectid  andindex_id= @indexid; SELECT @partitioncount = Count(*)         fromsys.partitionsWHERE object_id = @objectid  andindex_id= @indexid; --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(Ten)); EXEC(@command); PRINTN'Executed:' + @command; END; --Close and deallocate the cursor.CLOSEpartitions;deallocatepartitions;--Drop the temporary table.DROP TABLE#work_to_do;GO