EXEMPLO_1212310 Database

Parameters

Name	Type	Mode
@dias	varchar	IN

Definition

CREATE PROCEDURE p_ExpurgoLog(
/*Version=07.08.19*/
@dias VARCHAR(1000) = NULL)
AS
DECLARE
@SchemaOwner NVARCHAR(50),
@SQLCOMMAND NVARCHAR(MAX)
SELECT @SchemaOwner = AUDITSCHEMANAME FROM ZAUDITCONFIG
IF @DIAS IS NULL
SELECT @DIAS = LOGSDAYLIFETIME FROM ZAUDITCONFIG WHERE LOGSDAYLIFETIME >0
IF @DIAS IS NULL
BEGIN
PRINT 'É NECESSÁRIO INFORMAR A QUANTIDADE DE DIAS QUE O LOG DEVERÁ SER MANTIDO'
RETURN
END
/* FAZ LIMPEZA DA ZAUDITCHANGES */
IF NOT EXISTS (SELECT 1 FROM SYS.TABLES T INNER JOIN SYS.INDEXES I ON I.OBJECT_ID = T.OBJECT_ID WHERE I.NAME = 'IXZAUDITCHANGESAUDITDATE' AND T.SCHEMA_ID=SCHEMA_ID(@SchemaOwner))
BEGIN
SET @SQLCOMMAND = N'CREATE INDEX IXZAUDITCHANGESAUDITDATE ON ' + @SchemaOwner + '.ZAUDITCHANGES(AUDITDATE)'
EXEC SP_EXECUTESQL @SQLCOMMAND
END
/* FAZ LIMPEZA DA ZAUDITCHANGES */
IF NOT EXISTS (SELECT 1 FROM SYS.TABLES T INNER JOIN SYS.INDEXES I ON I.OBJECT_ID = T.OBJECT_ID WHERE I.NAME = 'IXZAUDITCHANGESAUDITOBJECTNAME' AND T.SCHEMA_ID=SCHEMA_ID(@SchemaOwner))
BEGIN
SET @SQLCOMMAND = N'CREATE INDEX IXZAUDITCHANGESAUDITOBJECTNAME ON ' + @SchemaOwner + '.ZAUDITCHANGES(AUDITOBJECTNAME)'
EXEC SP_EXECUTESQL @SQLCOMMAND
END
DECLARE @TABLENAME VARCHAR(100),
        @TABLENAME2 VARCHAR(100),
        @INDEXNAME VARCHAR(100),
        @TRIGGERNAME VARCHAR(100)
    SET @SQLCOMMAND = 'DECLARE @r INT;
                       SET @r = 1;
                       WHILE @r > 0
                       BEGIN
                         BEGIN TRANSACTION;
                         DELETE TOP (20000) ' + @SchemaOwner + '.ZAUDITCHANGES WHERE AUDITDATE<= cast(getdate()-' + @dias + ' As Date)
                         SET @r = @@ROWCOUNT;
                         COMMIT TRANSACTION;
                         CHECKPOINT;    -- if simple
                         -- BACKUP LOG ... -- if full
                       END'
    BEGIN TRY
    --PRINT @sqlcommand
    EXEC sp_executesql @SQLCommand
    END TRY
    BEGIN CATCH
    SELECT ERROR_MESSAGE(), substring(@SQLCommand,1,128), ERROR_NUMBER()
        IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION;
    END CATCH
    IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;
/* CRIA INDEX NA AUDITID */
    DECLARE exec_commands CURSOR FOR
    select name from sys.tables where schema_id=schema_id(@SchemaOwner) AND NAME NOT LIKE 'ZAUDITCHANGES%'
--  ' + @SchemaOwner '.PFUNC_log
    OPEN exec_commands
    FETCH NEXT FROM exec_commands
    INTO @TABLENAME
    WHILE @@FETCH_STATUS = 0
    BEGIN
    SET @SQLCOMMAND =  'IF NOT EXISTS (SELECT 1 FROM SYS.TABLES T INNER JOIN SYS.INDEXES I ON I.OBJECT_ID = T.OBJECT_ID WHERE I.NAME = ''' + 'IX' + @TABLENAME + 'AUDITID''' + ' AND T.SCHEMA_ID=SCHEMA_ID(''' + @SchemaOwner + '''))' + CHAR(10)
    SET @SQLCOMMAND += 'CREATE INDEX IX' + @TABLENAME + 'AUDITID ON ' + @SchemaOwner + '.' + @TABLENAME + '(AUDITID)'
    --SET @SQLCOMMAND =  'DROP INDEX IX' + @TABLENAME + 'AUDITID ON ' + @SchemaOwner '.' + @TABLENAME
    BEGIN TRY
    --PRINT @sqlcommand
    EXEC (@SQLCommand)
    END TRY
    BEGIN CATCH
    SELECT ERROR_MESSAGE(), substring(@SQLCommand,1,128), ERROR_NUMBER()
    END CATCH
    FETCH NEXT FROM exec_commands
        INTO @TABLENAME
    END
    CLOSE exec_commands;
    DEALLOCATE exec_commands;
/* LIMPA TABELAS */
    DECLARE exec_commands CURSOR FOR
    select name from sys.tables where schema_id=schema_id(@SchemaOwner) AND NAME NOT LIKE 'ZAUDITCHANGES%'
    OPEN exec_commands
    FETCH NEXT FROM exec_commands
    INTO @TABLENAME
    WHILE @@FETCH_STATUS = 0
    BEGIN
    --SET @SQLCOMMAND =  'DELETE FROM ' + @SchemaOwner '.' + @TABLENAME + ' WHERE NOT EXISTS (SELECT 1 FROM ' + @SchemaOwner '.ZAUDITCHANGES Z WHERE Z.AUDITID = ' + @SchemaOwner '.' + @TABLENAME + '.AUDITID)'
    SET @SQLCOMMAND = 'DECLARE @r INT;
                       SET @r = 1;
                       WHILE @r > 0
                       BEGIN
                         BEGIN TRANSACTION;
                         DELETE TOP (20000) ' + @SchemaOwner + '.' + @TABLENAME + ' WHERE NOT EXISTS (SELECT 1 FROM ' + @SchemaOwner + '.ZAUDITCHANGES Z WHERE Z.AUDITID = ' + @SchemaOwner + '.' + @TABLENAME + '.AUDITID AND Z.AUDITOBJECTNAME = ''' + @TABLENAME + ''')
                         SET @r = @@ROWCOUNT;
                         COMMIT TRANSACTION;
                         CHECKPOINT;    -- if simple
                         -- BACKUP LOG ... -- if full
                       END'
    BEGIN TRY
    --PRINT @sqlcommand
    EXEC sp_executesql @SQLCommand
    END TRY
    BEGIN CATCH
    SELECT ERROR_MESSAGE(), substring(@SQLCommand,1,128), ERROR_NUMBER()
        IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION;
    END CATCH
    IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;
    FETCH NEXT FROM exec_commands
        INTO @TABLENAME
    END
    CLOSE exec_commands;
    DEALLOCATE exec_commands;
/* HABILITA TRIGGERS */
    DECLARE exec_commands CURSOR FOR
    SELECT name, object_name(parent_id) FROM SYS.triggers WHERE parent_id IN (SELECT OBJECT_ID(OBJECTNAME) FROM ZAUDITITEMS WHERE STATUS=1)
    OPEN exec_commands
    FETCH NEXT FROM exec_commands
    INTO @TRIGGERNAME, @TABLENAME
    WHILE @@FETCH_STATUS = 0
    BEGIN
    SET @SQLCOMMAND = 'ALTER TABLE ' + @TABLENAME + ' ENABLE TRIGGER ' + @TRIGGERNAME
    BEGIN TRY
    --PRINT @sqlcommand
    EXEC sp_executesql @SQLCommand
    END TRY
    BEGIN CATCH
    SELECT ERROR_MESSAGE(), substring(@SQLCommand,1,128), ERROR_NUMBER()
    END CATCH
    FETCH NEXT FROM exec_commands
        INTO @TRIGGERNAME, @TABLENAME
    END
    CLOSE exec_commands;
    DEALLOCATE exec_commands;