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

CREATE PROCEDURE Sp_Sql_Update_Statistics
AS
DECLARE @Tab varchar(30)
DECLARE @Lin int

SET NOCOUNT ON
DECLARE cursor_upd_stat cursor
FOR SELECT name from sysobjects 
    WHERE  type = 'u' 
      AND uid = 1 
ORDER BY name

OPEN cursor_upd_stat
FETCH cursor_upd_stat INTO @Tab
WHILE (@@FETCH_STATUS = 0)
BEGIN
    /*****************************************/
    /* TRATAMENTO DE TABELAS NOVAS           */
    /*****************************************/
    IF NOT EXISTS 
       (SELECT Tabela 
        FROM Sql_Update_Statistics 
        WHERE Tabela = @Tab)
    BEGIN
        INSERT INTO Sql_Update_Statistics(Tabela,Linhas, Hora_Inicio)
        SELECT @Tab, Rows , GetDate()
        FROM sysindexes i, sysobjects o
        WHERE o.name  =  @Tab
          AND indid in (0,1)
          AND o.id  =  i.id
	UPDATE Sql_Update_Statistics 
           SET Hora_Inicio = getdate(),
               Caso        = "TABELA NOVA"
         WHERE Tabela = @Tab
        EXEC ("UPDATE STATISTICS "+ @Tab)
	EXECUTE sp_recompile @Tab
        UPDATE Sql_Update_Statistics SET Hora_Fim = getdate() WHERE Tabela = @Tab
        UPDATE Sql_Update_Statistics 
           SET Linhas = (SELECT rows 
                           FROM sysindexes i, sysobjects o
                           WHERE o.name  =  @Tab
                            AND indid in (0,1)
                            AND o.id  =  i.id),
               Tempo_Gasto_Seg = datediff(ss, Hora_Inicio, Hora_Fim)
        WHERE Tabela = @Tab
    END
 
    ELSE
    /*********************************************/
    /* TRATAMENTO DE TABELAS QUE TIVERAM MAIS    */
    /* DE 10% DE CRESCIMENTO NO NUMERO DE LINHAS */
    /*********************************************/
    IF (SELECT rows 
        FROM sysindexes i, sysobjects o
        WHERE o.name  =  @Tab
          AND indid in (0,1)
          AND o.id  =  i.id)  > 
         1.1 * (SELECT Linhas FROM Sql_Update_Statistics
               WHERE Tabela = @Tab)
    BEGIN
        UPDATE Sql_Update_Statistics 
           SET Hora_Inicio = getdate() ,
               Caso        = "AUMENTOU 10%"
         WHERE Tabela = @Tab
        EXEC ("UPDATE STATISTICS " + @Tab)
	EXECUTE sp_recompile @Tab
        UPDATE Sql_Update_Statistics SET Hora_Fim = getdate() WHERE Tabela = @Tab
        UPDATE Sql_Update_Statistics 
           SET Linhas = (SELECT rows 
                           FROM sysindexes i, sysobjects o
                          WHERE o.name  =  @Tab
                            AND indid in (0,1)
                            AND o.id  =  i.id),
               Tempo_Gasto_Seg = datediff(ss, Hora_Inicio, Hora_Fim)
        WHERE Tabela = @Tab
    END
    ELSE
    /*******************************************/
    /* TRATAMENTO DE TABELAS QUE TIVERAM MAIS  */
    /* DE 10% DE DIMINICAO NO NUMERO DE LINHAS */
    /*******************************************/
    IF (SELECT rows 
        FROM sysindexes i, sysobjects o
        WHERE o.name  =  @Tab
          AND indid in (0,1)
          AND o.id  =  i.id)  < 
         0.9 * (SELECT Linhas FROM Sql_Update_Statistics
               WHERE Tabela = @Tab)
    BEGIN
        UPDATE Sql_Update_Statistics 
           SET Hora_Inicio = getdate() ,
               Caso        = "DIMINUIU 10%"
         WHERE Tabela = @Tab
        EXEC ("UPDATE STATISTICS " + @Tab)
	EXECUTE sp_recompile @Tab
        UPDATE Sql_Update_Statistics SET Hora_Fim = getdate() WHERE Tabela = @Tab
        UPDATE Sql_Update_Statistics 
           SET Linhas = (SELECT rows
                           FROM sysindexes i, sysobjects o
                          WHERE o.name  =  @Tab
                            AND indid in (0,1)
                            AND o.id  =  i.id),
               Tempo_Gasto_Seg = datediff(ss, Hora_Inicio, Hora_Fim)
        WHERE Tabela = @Tab
    END
    /***********************************************/
    /* TRATAMENTO DE TABELAS QUE NAO SAO ACERTADAS */
    /* A MAIS DE UM MES                            */
    /***********************************************/
    IF (SELECT DATEDIFF (DD, Hora_Inicio, getdate()) 
          FROM Sql_Update_Statistics
         WHERE Tabela = @Tab) > 30
    BEGIN
        UPDATE Sql_Update_Statistics 
           SET Hora_Inicio = getdate() , 
               Caso        = "UM MES SEM UPDATE STATISTICS"
         WHERE Tabela = @Tab
        EXEC ("UPDATE STATISTICS " + @Tab)
	EXECUTE sp_recompile @Tab
        UPDATE Sql_Update_Statistics SET Hora_Fim = getdate() WHERE Tabela = @Tab
        UPDATE Sql_Update_Statistics 
           SET Linhas = (SELECT rows 
                           FROM sysindexes i, sysobjects o
                          WHERE o.name  =  @Tab
                            AND indid in (0,1)
                            AND o.id  =  i.id),
               Tempo_Gasto_Seg = datediff(ss, Hora_Inicio, Hora_Fim)
        WHERE Tabela = @Tab
    END

    FETCH cursor_upd_stat INTO @Tab
END
CLOSE      cursor_upd_stat 
DEALLOCATE cursor_upd_stat 
SET NOCOUNT OFF



GO