Statistics in all Databases

  • Hi,

    I am using below query to update the Statistics in one Db,

    i need to update it for around 100 Db

    Could you please check this and advise

    DECLARE @tblName sysname, @sql nvarchar(100)

    DECLARE @tbl TABLE

    (name sysname)

    INSERT @tbl

    SELECT name

    FROM sysobjects

    WHERE xtype = 'U' and uid = 1

    WHILE EXISTS(select top 1 * from @tbl)

    BEGIN

    SELECT TOP 1 @tblName = name from @tbl

    SET @sql = 'UPDATE STATISTICS ' + @tblName + ' WITH SAMPLE 50 PERCENT'

    EXEC sp_executesql @sql

    -- PRINT @sql

    DELETE @tbl WHERE name = @tblName

    END

  • This will work:

    EDIT: No it won't! Bad code removed. See my next post.



    Scott Duncan

    MARCUS. Why dost thou laugh? It fits not with this hour.
    TITUS. Why, I have not another tear to shed;
    --Titus Andronicus, William Shakespeare


  • Thanks,very helpfull

  • This is even more helpful - code that actually updates stats in all databases. My bad, I missed that the first sp_executesql statement changes the context, but only for that statement, so that code ends up updating stats in the same database over and over (d'oh).

    This code works (I promise!):

    [font="Courier New"]DECLARE @dbname sysname

    DECLARE @sql nvarchar(4000)

    DECLARE tmpcursor CURSOR FAST_FORWARD FOR

    SELECT name FROM master..sysdatabases --for 2005 use master.sys.databases

    WHERE DATABASEPROPERTYEX(name, 'Status') = 'ONLINE' -- for 2005, can use WHERE state_desc = 'ONLINE'

    AND name <> 'tempdb'

    OPEN tmpcursor

    FETCH NEXT FROM tmpcursor INTO @dbname

    WHILE @@FETCH_STATUS = 0

    BEGIN

    SET @sql = N'USE [' + @dbname + N']; '

    + N'SELECT DB_NAME(); '

    + N'DECLARE @tbl TABLE (name sysname); DECLARE @sql2 nvarchar(100), @tblName sysname; '

    + N'INSERT @tbl '

    + N' SELECT name '

    + N' FROM sysobjects '

    + N' WHERE xtype = ''U'' and uid = 1; '

    + N'WHILE EXISTS(SELECT TOP 1 * FROM @tbl) '

    + N'BEGIN '

    + N' SELECT TOP 1 @tblName = name FROM @tbl '

    + N' SET @sql2 = N''UPDATE STATISTICS ['' + @tblName + N''] WITH SAMPLE 50 PERCENT '' '

    + N' EXEC sp_executesql @sql2 '

    + N' DELETE @tbl WHERE name = @tblName '

    + N'END '

    EXEC sp_executesql @sql

    FETCH NEXT FROM tmpcursor INTO @dbname

    END

    CLOSE tmpcursor

    DEALLOCATE tmpcursor[/font]

    It may be able to be further refined, but it does the job. Not sure how wise it is to run nested sp_executesql statements, but nothing went horribly wrong on my test rig (SQL 2005 Dev).

    Set your results window to text output - will give you the dbname followed by rowcounts.



    Scott Duncan

    MARCUS. Why dost thou laugh? It fits not with this hour.
    TITUS. Why, I have not another tear to shed;
    --Titus Andronicus, William Shakespeare


Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply