SQL Server Performance

How can I update statistics of all tables of a database???

Discussion in 'SQL Server 2005 General DBA Questions' started by nis_swiss, Jan 22, 2008.

  1. nis_swiss New Member

    EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?')"GOEXEC sp_MSforeachtable @command1="print '?' UPDATE STATISTICS ('?')" GOthis is the query i wrote for reindexing n then updating the statistics of all tables within a database.the first query worked well n fine but the second one is giving me error:Msg 170, Level 15, State 1, Line 1Line 1: Incorrect syntax near '('.now I am facing problem as what next to do to achieve what I want to do in a single stroke....can anyone tell me why I am getting this error????And if someone has other way to do the same, can u please tell me????Thanks in advance.......
  2. ghemant Moderator

    You can execute sp_updatestats which will update statistics for all tables of the current database, as you may have read dbcc reinex may be removed in next version so start practising using alter index statement refer BOL for more on ALTER INDEX statement, and AFAIK if you are reindexing you don't have to update the statistics as reindexing will update them as well. You can run sp_updatestats which will update the statistics of all the tables in current table.
    Regards
  3. satya Moderator

    See this http://www.sql-server-performance.com/articles/per/automatic_reindexing_sql2000_p1.aspx that refers to SQL 2000 and still works with 2005. If not below script should do:
    CREATE TABLE #IndexFragmentation (
    ObjectName char(255),
    ObjectId int,
    IndexName char(255),
    IndexId int,
    Lvl int,
    CountPages int,
    CountRows int,
    MinRecSize int,
    MaxRecSize int,
    AvgRecSize int,
    ForRecCount int,
    Extents int,
    ExtentSwitches int,
    AvgFreeBytes int,
    AvgPageDensity int,
    ScanDensity decimal,
    BestCount int,
    ActualCount int,
    LogicalFrag decimal,
    ExtentFrag decimal)
    INSERT #IndexFragmentation
    EXEC ('DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES')
    DELETE #IndexFragmentation where left(ObjectName,3)='sys'
    UPDATE #IndexFragmentation set ObjectName=s.name+'.'+o.name
    FROM #IndexFragmentation i
    JOIN sys.objects o ON i.ObjectID = o.object_id
    JOIN sys.schemas s ON o.schema_id = s.schema_id
    DECLARE @Table sysname, @DynamicSQL varchar(512)
    DECLARE @objectid int, @indexid int
    DECLARE @Fragmentation decimal, @MaxFragmentation decimal
    SET @MaxFragmentation = 35.0
    DECLARE curIndexFrag CURSOR FOR
    SELECT ObjectName, LogicalFrag = max(LogicalFrag)
    FROM #IndexFragmentation
    WHERE LogicalFrag >= @MaxFragmentation
    AND indexid != 0 AND indexid != 255
    GROUP BY ObjectName

    OPEN curIndexFrag
    FETCH NEXT
    FROM curIndexFrag
    INTO @Table, @Fragmentation
    WHILE @@FETCH_STATUS = 0
    BEGIN
    SELECT @DynamicSQL = 'DBCC DBREINDEX (''' +RTRIM(@Table) + ''', '''', 70)'
    PRINT @DynamicSQL
    EXEC (@DynamicSQL)
    FETCH NEXT
    FROM curIndexFrag
    INTO @Table, @Fragmentation
    END

    CLOSE curIndexFrag
    DEALLOCATE curIndexFrag
    DROP TABLE #IndexFragmentation
    GO

  4. Greg Larsen New Member

    Try this:EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?')" GOEXEC sp_MSforeachtable @command1="print '?' UPDATE STATISTICS ?"
    GO

Share This Page