if exists (select * from sysobjects where id =
object_id('dbo.sp_update_statistics_all') and sysstat & 0xf = 4)
drop procedure dbo.sp_update_statistics_all
GO

CREATE PROCEDURE sp_update_statistics_all
AS
/*

This procedure will run UPDATE STATISTICS against
all user-defined tables on all database.
*/

DECLARE @tablename varchar(30)
DECLARE @tablename_header varchar(75)
DECLARE @dbname varchar(30)
DECLARE @dbname_header varchar(75)
DECLARE dbnames_cursor CURSOR FOR SELECT name FROM sysdatabases
        WHERE name not in ('master', 'pubs', 'tempdb', 'model')

OPEN dbnames_cursor

  FETCH NEXT FROM dbnames_cursor INTO @dbname

  WHILE (@@fetch_status <> -1)
    BEGIN
      IF (@@fetch_status = -2)
        BEGIN
          FETCH NEXT FROM dbnames_cursor INTO @dbname
          CONTINUE
        END
         
      SELECT @dbname_header = "Database " + RTRIM(UPPER(@dbname))
      PRINT " "
      PRINT @dbname_header
      PRINT " "

      EXEC ("USE " + @dbname + " DECLARE tnames_cursor CURSOR FOR SELECT
name from sysobjects where type = 'U'")

      OPEN tnames_cursor

      FETCH NEXT FROM tnames_cursor INTO @tablename

      WHILE (@@fetch_status <> -1)
        BEGIN
          IF (@@fetch_status = -2)
            BEGIN
              FETCH NEXT FROM tnames_cursor INTO @tablename
              CONTINUE
            END
    
          SELECT @tablename_header = "  Updating " +
RTRIM(UPPER(@tablename))
          PRINT @tablename_header
          EXEC ("UPDATE STATISTICS " + @dbname + ".." + @tablename )
          EXEC ("USE " + @dbname + " EXEC sp_recompile " + @tablename )
          FETCH NEXT FROM tnames_cursor INTO @tablename
        END

      DEALLOCATE tnames_cursor
      FETCH NEXT FROM dbnames_cursor INTO @dbname
    END

DEALLOCATE dbnames_cursor

PRINT " "
PRINT " "
PRINT "Statistics have been updated for all user-defined tables."

GO