EXEMPLO_1212310 Database

Parameters

Name	Type	Mode
@TableName	nvarchar	IN
@AuditOperation	nvarchar	IN
@Compression	int	IN

Definition

CREATE PROCEDURE p_TableTriggerGenerator(
/*Version=07.08.19*/
@TableName sysname = null,
@AuditOperation nvarchar(3) = '',
@Compression int = 0 ) /* C=Create D=Drop A=Append New Fields R=Remove Old Fields T=Truncate */
AS
DECLARE
    @Owner sysname,
    @DataSpace VARCHAR(30),
    @IndexSpace VARCHAR(30),
    @ListOfFields nvarchar(max) = '',
    @SqlCommand nvarchar(max),
    @ColumnName varchar(128),
    @ColumnType varchar(128),
    @ColumnLength INT,
    @ColumnNullable int,
    @ColumnCollation varchar(128),
    @ColumnPrecision tinyint,
    @ColumnScale tinyint,
    @ColumnUserType int,
    @ObjectId Int,
    @CurrentSchema varchar(64) = schema_name(),
    @Compress bit = CASE WHEN REPLACE(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR),'.','') < '140000000' THEN 0 ELSE 1 END,
    @TableCompressed VARCHAR(30),
    @IndexCompressed VARCHAR(30)
    SET @SqlCommand = 'SELECT @Owner = AUDITSCHEMANAME, @DataSpace = DATASPACE, @IndexSpace = INDEXSPACE FROM ' + @CurrentSchema + '.ZAUDITCONFIG'
    /* Se o parametro @Compression = 0 não faz compression da tabela*/
    SET @Compress = @Compress * @Compression
    EXECUTE sp_executesql @SqlCommand, N'@Owner sysname out, @DataSpace varchar(30) out, @IndexSpace varchar(30) out', @Owner out, @DataSpace out, @indexSpace out
        /*Adiciona novos campos das tabelas originais nas tabelas de auditoria*/
        IF @AuditOperation = 'A'
        BEGIN
        DECLARE TableColumns CURSOR READ_ONLY FOR
        SELECT C2.COLUMN_NAME, CASE WHEN C2.DOMAIN_NAME IS NULL THEN C2.DATA_TYPE ELSE C2.DOMAIN_NAME END AS TYPENAME, C2.CHARACTER_MAXIMUM_LENGTH, CASE WHEN C2.IS_NULLABLE = 'YES' THEN 1 ELSE 0 END, C2.COLLATION_NAME, C2.NUMERIC_PRECISION, C2.NUMERIC_SCALE, CASE WHEN C2.DOMAIN_NAME IS NULL THEN 1 ELSE 0 END ISSYSTEM
        FROM [INFORMATION_SCHEMA].[COLUMNS] C2
        WHERE TABLE_NAME= @TableName
              AND TABLE_SCHEMA = @CurrentSchema
              AND COLUMNPROPERTY(OBJECT_ID(C2.TABLE_SCHEMA+'.'+C2.TABLE_NAME),C2.COLUMN_NAME,'ISCOMPUTED') = 0
              AND DATA_TYPE NOT IN (select name from sys.types where system_type_id in (select system_type_id from sys.types where system_type_id = user_type_id and name in ('TEXT','NTEXT','IMAGE','TIMESTAMP')))
              AND COLUMN_NAME NOT IN ('RECCREATEDBY', 'RECCREATEDON', 'RECMODIFIEDBY', 'RECMODIFIEDON')
        AND C2.COLUMN_NAME NOT IN (SELECT COLUMN_NAME
            FROM [INFORMATION_SCHEMA].[COLUMNS]
            WHERE TABLE_NAME= @TableName
            AND TABLE_SCHEMA = @Owner
            AND COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA+'.'+TABLE_NAME),COLUMN_NAME,'ISCOMPUTED') = 0)
        ORDER BY COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA+'.'+TABLE_NAME),COLUMN_NAME,'ColumnId')
        OPEN TableColumns
            FETCH NEXT FROM TableColumns
            INTO @ColumnName, @ColumnType, @ColumnLength, @ColumnNullable, @ColumnCollation, @ColumnPrecision, @ColumnScale, @ColumnUserType
            WHILE @@FETCH_STATUS = 0
            BEGIN
                IF @ListOfFields = ''
                SET @ListOfFields += CHAR(10) + '[' + @ColumnName + '] ' + ISNULL(@ColumnType + CASE WHEN @ColumnType IN ('NUMBER','NUMERIC','DECIMAL') THEN '(' + CAST(@ColumnPrecision AS VARCHAR) + ',' + CAST(@ColumnScale AS VARCHAR) + ')' WHEN @ColumnType LIKE 'DATE%' THEN '' WHEN @ColumnType LIKE 'MONEY' THEN '' WHEN @ColumnType LIKE 'BIT' THEN '' WHEN @ColumnUserType = 0 THEN '' WHEN @ColumnType LIKE '%INT%' THEN '' WHEN @ColumnType LIKE '%BINARY%' THEN +'(' + CASE WHEN @ColumnLength = -1 THEN 'MAX' ELSE CAST(@ColumnLength AS VARCHAR) END + ')' WHEN @ColumnType like '%CHAR%' AND @ColumnUserType = 1 THEN '(' + CASE WHEN @ColumnLength = -1 THEN 'MAX' ELSE CAST(CASE WHEN @ColumnLength = 8000 AND @ColumnType = 'NVARCHAR' THEN 4000 WHEN @ColumnLength = '-1' THEN 'MAX' ELSE @ColumnLength END AS VARCHAR) END + ') COLLATE ' + @ColumnCollation ELSE '' END, '') + ' NULL'
                ELSE
                SET @ListOfFields += CHAR(10) + ',[' + @ColumnName + '] ' + ISNULL(@ColumnType + CASE WHEN @ColumnType IN ('NUMBER','NUMERIC','DECIMAL') THEN '(' + CAST(@ColumnPrecision AS VARCHAR) + ',' + CAST(@ColumnScale AS VARCHAR) + ')' WHEN @ColumnType LIKE 'DATE%' THEN '' WHEN @ColumnType LIKE 'MONEY%' THEN '' WHEN @ColumnType LIKE 'BIT' THEN '' WHEN @ColumnUserType = 0 THEN '' WHEN @ColumnType LIKE '%INT%' THEN '' WHEN @ColumnType LIKE '%BINARY%' THEN +'(' + CASE WHEN @ColumnLength = -1 THEN 'MAX' ELSE CAST(@ColumnLength AS VARCHAR) END + ')' WHEN @ColumnType like '%CHAR%' AND @ColumnUserType = 1 THEN '(' + CASE WHEN @ColumnLength = -1 THEN 'MAX' ELSE CAST(CASE WHEN @ColumnLength = 8000 AND @ColumnType = 'NVARCHAR' THEN 4000 WHEN @ColumnLength = '-1' THEN 'MAX' ELSE @ColumnLength END AS VARCHAR) END + ') COLLATE ' + @ColumnCollation ELSE '' END, '') + ' NULL'
            FETCH Next FROM TableColumns
            INTO @ColumnName, @ColumnType, @ColumnLength, @ColumnNullable, @ColumnCollation, @ColumnPrecision, @ColumnScale, @ColumnUserType
            END
        CLOSE TableColumns
        DEALLOCATE TableColumns
            IF @ListOfFields != ''
            BEGIN
            set @SQLCommand =  'IF EXISTS (SELECT 1 FROM sys.tables WHERE name = ''' + @TableName + ''' and SCHEMA_NAME(schema_id) = ''' + @owner + '''' + ')' + CHAR(10)
            set @SQLCommand +=  'ALTER TABLE ' + @Owner + '.' + @TableName + ' ADD'
            set @SQLCommand += @ListOfFields + CHAR(10)
            /*PRINT (@SQLCommand)*/
            EXECUTE sp_executesql @SQLCommand
            END
        END
        /*Remove campos excluidos das tabelas originais das tabelas de auditoria*/
        IF @AuditOperation = 'R'
        BEGIN
            SET @ListOfFields = ''
        DECLARE TableColumns CURSOR READ_ONLY FOR
        SELECT C2.COLUMN_NAME, CASE WHEN C2.DOMAIN_NAME IS NULL THEN C2.DATA_TYPE ELSE C2.DOMAIN_NAME END AS TYPENAME, C2.CHARACTER_MAXIMUM_LENGTH, CASE WHEN C2.IS_NULLABLE = 'YES' THEN 1 ELSE 0 END, C2.COLLATION_NAME, C2.NUMERIC_PRECISION, C2.NUMERIC_SCALE, CASE WHEN C2.DOMAIN_NAME IS NULL THEN 1 ELSE 0 END ISSYSTEM
        FROM [INFORMATION_SCHEMA].[COLUMNS] C2
        WHERE TABLE_NAME= @TableName
              AND TABLE_SCHEMA = @Owner
              AND COLUMNPROPERTY(OBJECT_ID(C2.TABLE_SCHEMA+'.'+C2.TABLE_NAME),C2.COLUMN_NAME,'ISCOMPUTED') = 0
              AND COLUMN_NAME NOT IN ('AUDITID','AUDITACTION','PARENTLOGID','LOGID','LOGAPP','LOGUSER')
        AND C2.COLUMN_NAME NOT IN (SELECT COLUMN_NAME
            FROM [INFORMATION_SCHEMA].[COLUMNS]
            WHERE TABLE_NAME= @TableName
            AND TABLE_SCHEMA = @CurrentSchema
            AND DATA_TYPE NOT IN (select name from sys.types where system_type_id in (select system_type_id from sys.types where system_type_id = user_type_id and name in ('TEXT','NTEXT','IMAGE','TIMESTAMP')))
            AND COLUMN_NAME NOT IN ('RECCREATEDBY', 'RECCREATEDON', 'RECMODIFIEDBY', 'RECMODIFIEDON')
            AND COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA+'.'+TABLE_NAME),COLUMN_NAME,'ISCOMPUTED') = 0)
        ORDER BY COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA+'.'+TABLE_NAME),COLUMN_NAME,'ColumnId')
        OPEN TableColumns
            FETCH NEXT FROM TableColumns
            INTO @ColumnName, @ColumnType, @ColumnLength, @ColumnNullable, @ColumnCollation, @ColumnPrecision, @ColumnScale, @ColumnUserType
            WHILE @@FETCH_STATUS = 0
            BEGIN
                IF @ListOfFields = ''
                SET @ListOfFields += CHAR(10) + '[' + @ColumnName + ']';
                ELSE
                SET @ListOfFields += CHAR(10) + ',[' + @ColumnName + ']';
            FETCH Next FROM TableColumns
            INTO @ColumnName, @ColumnType, @ColumnLength, @ColumnNullable, @ColumnCollation, @ColumnPrecision, @ColumnScale, @ColumnUserType
            END
        CLOSE TableColumns
        DEALLOCATE TableColumns
            IF @ListOfFields != ''
            BEGIN
            set @SQLCommand =  'IF EXISTS (SELECT 1 FROM sys.tables WHERE name = ''' + @TableName + ''' and SCHEMA_NAME(schema_id) = ''' + @owner + '''' + ')' + CHAR(10)
            set @SQLCommand +=  'ALTER TABLE ' + @Owner + '.' + @TableName + ' DROP COLUMN'
            set @SQLCommand += @ListOfFields + CHAR(10)
            /*PRINT (@SQLCommand)*/
            EXECUTE sp_executesql @SQLCommand
            END
            SET @ListOfFields = ''
        END
        /*Altera tipo de dados dos campos alterados na tabela original*/
        IF @AuditOperation = 'M'
        BEGIN
            SET @ListOfFields = ''
        DECLARE TableColumns CURSOR READ_ONLY FOR
        SELECT C2.COLUMN_NAME, CASE WHEN C2.DOMAIN_NAME IS NULL THEN C2.DATA_TYPE ELSE C2.DOMAIN_NAME END AS TYPENAME, C2.CHARACTER_MAXIMUM_LENGTH, CASE WHEN C2.IS_NULLABLE = 'YES' THEN 1 ELSE 0 END, C2.COLLATION_NAME, C2.NUMERIC_PRECISION, C2.NUMERIC_SCALE, CASE WHEN C2.DOMAIN_NAME IS NULL THEN 1 ELSE 0 END ISSYSTEM
        FROM [INFORMATION_SCHEMA].[COLUMNS] C2
        WHERE TABLE_NAME= @TableName
              AND TABLE_SCHEMA = @CurrentSchema
              AND COLUMNPROPERTY(OBJECT_ID(C2.TABLE_SCHEMA+'.'+C2.TABLE_NAME),C2.COLUMN_NAME,'ISCOMPUTED') = 0
              AND DATA_TYPE NOT IN (select name from sys.types where system_type_id in (select system_type_id from sys.types where system_type_id = user_type_id and name in ('TEXT','NTEXT','IMAGE','TIMESTAMP')))
              AND COLUMN_NAME NOT IN ('RECCREATEDBY', 'RECCREATEDON', 'RECMODIFIEDBY', 'RECMODIFIEDON')
              AND COLUMN_NAME IN (SELECT COLUMN_NAME
                                     FROM [INFORMATION_SCHEMA].[COLUMNS]
                                     WHERE TABLE_NAME= @TableName
                                     AND TABLE_SCHEMA = @Owner
                                     AND COLUMN_NAME NOT IN ('AUDITID','AUDITACTION','PARENTLOGID','LOGID','LOGAPP','LOGUSER')
                                     AND COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA+'.'+TABLE_NAME),COLUMN_NAME,'ISCOMPUTED') = 0)
        EXCEPT
        SELECT C2.COLUMN_NAME, CASE WHEN C2.DOMAIN_NAME IS NULL THEN C2.DATA_TYPE ELSE C2.DOMAIN_NAME END AS TYPENAME, C2.CHARACTER_MAXIMUM_LENGTH, CASE WHEN C2.IS_NULLABLE = 'YES' THEN 1 ELSE 0 END, C2.COLLATION_NAME, C2.NUMERIC_PRECISION, C2.NUMERIC_SCALE, CASE WHEN C2.DOMAIN_NAME IS NULL THEN 1 ELSE 0 END ISSYSTEM
            FROM [INFORMATION_SCHEMA].[COLUMNS] C2
            WHERE TABLE_NAME= @TableName
            AND TABLE_SCHEMA = @Owner
            AND COLUMN_NAME NOT IN ('AUDITID','AUDITACTION','PARENTLOGID','LOGID')
            AND COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA+'.'+TABLE_NAME),COLUMN_NAME,'ISCOMPUTED') = 0
        OPEN TableColumns
            FETCH NEXT FROM TableColumns
            INTO @ColumnName, @ColumnType, @ColumnLength, @ColumnNullable, @ColumnCollation, @ColumnPrecision, @ColumnScale, @ColumnUserType
            WHILE @@FETCH_STATUS = 0
            BEGIN
                SET @ListOfFields = '[' + @ColumnName + '] ' + @ColumnType + ISNULL(CASE WHEN @ColumnType IN ('NUMBER','NUMERIC','DECIMAL') THEN '(' + CAST(@ColumnPrecision AS VARCHAR) + ',' + CAST(@ColumnScale AS VARCHAR) + ')' WHEN @ColumnType LIKE 'DATE%' THEN '' WHEN @ColumnType LIKE 'MONEY' THEN '' WHEN @ColumnType LIKE 'BIT' THEN '' WHEN @ColumnUserType = 0 THEN '' WHEN @ColumnType LIKE '%INT%' THEN '' WHEN @ColumnType LIKE '%BINARY%' THEN +'(' + CASE WHEN @ColumnLength = -1 THEN 'MAX' ELSE CAST(@ColumnLength AS VARCHAR) END + ')' WHEN @ColumnType like '%CHAR%' AND @ColumnUserType = 1 THEN '(' + CASE WHEN @ColumnLength = -1 THEN 'MAX' ELSE CAST(CASE WHEN @ColumnLength = 8000 AND @ColumnType = 'NVARCHAR' THEN 4000 ELSE @ColumnLength END AS VARCHAR) END + ') COLLATE ' + @ColumnCollation ELSE '' END, '') + ' NULL'
                SET @SQLCommand = 'ALTER TABLE ' + @Owner + '.' + @TableName + ' ALTER COLUMN ' + @ListOfFields
                /*PRINT @SQLCommand*/
                EXEC sp_executesql @SQLCommand
            FETCH Next FROM TableColumns
            INTO @ColumnName, @ColumnType, @ColumnLength, @ColumnNullable, @ColumnCollation, @ColumnPrecision, @ColumnScale, @ColumnUserType
            END
        CLOSE TableColumns
        DEALLOCATE TableColumns
        END
        /*Cria tabelas de auditoria baseado nas tabelas originais*/
        IF @AuditOperation = 'C'
        BEGIN
        /* Declare cursor para colunas da tabela */
        DECLARE TableColumns CURSOR READ_ONLY FOR
            select col.name, typ.name as typename, col.max_length, col.is_nullable, col.collation_name, col.precision, col.scale, col.system_type_id-col.user_type_id
            from sys.tables tbl(nolock)
                    inner join sys.columns col(nolock)
                        inner join sys.types typ(nolock)
                            on  col.user_type_id = typ.user_type_id
                                and col.name <> 'sysname'
                    on tbl.[object_id] = col.[object_id]
            where   OBJECT_NAME(tbl.[object_id]) = @TableName
                    and tbl.schema_id = SCHEMA_ID(@CurrentSchema)
                    and col.is_rowguidcol = 0
                    and col.is_computed = 0
                    and objectproperty(tbl.[object_id], N'IsUserTable') = 1
                    and typ.name not in (select name from sys.types where system_type_id in (select system_type_id from sys.types where system_type_id = user_type_id and name in ('TEXT','NTEXT','IMAGE','TIMESTAMP')))
                    and col.name not in ('RECCREATEDBY', 'RECCREATEDON', 'RECMODIFIEDBY', 'RECMODIFIEDON')
            order by col.column_id
        OPEN TableColumns
            FETCH NEXT FROM TableColumns
            INTO @ColumnName, @ColumnType, @ColumnLength, @ColumnNullable, @ColumnCollation, @ColumnPrecision, @ColumnScale, @ColumnUserType
            WHILE @@FETCH_STATUS = 0
            BEGIN
                SET @ListOfFields += CHAR(10) + ',[' + @ColumnName + '] ' + @ColumnType + ISNULL(CASE WHEN @ColumnType IN ('NUMBER','NUMERIC','DECIMAL') THEN '(' + CAST(@ColumnPrecision AS VARCHAR) + ',' + CAST(@ColumnScale AS VARCHAR) + ')' WHEN @ColumnType LIKE 'DATE%' THEN '' WHEN @ColumnType LIKE 'MONEY' THEN '' WHEN @ColumnType LIKE 'BIT' THEN '' WHEN @ColumnType LIKE '%INT%' THEN '' WHEN @ColumnType LIKE '%BINARY%' THEN +'(' + CASE WHEN @ColumnLength = -1 THEN 'MAX' ELSE CAST(@ColumnLength AS VARCHAR) END + ')' WHEN @ColumnType like '%CHAR%' AND @ColumnUserType = 0 THEN '(' + CASE WHEN @ColumnLength = -1 THEN 'MAX' ELSE CAST(CASE WHEN @ColumnLength = 8000 AND @ColumnType = 'NVARCHAR' THEN 4000 ELSE @ColumnLength END AS VARCHAR) END + ') COLLATE ' + @ColumnCollation WHEN @ColumnUserType != 0 THEN '' ELSE '' END, '') + CASE WHEN @ColumnNullable = 1 THEN ' NULL' ELSE ' NOT NULL' END
            FETCH Next FROM TableColumns
            INTO @ColumnName, @ColumnType, @ColumnLength, @ColumnNullable, @ColumnCollation, @ColumnPrecision, @ColumnScale, @ColumnUserType
            END
        CLOSE TableColumns
        DEALLOCATE TableColumns
            set @SQLCommand =  'IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = ''' + @TableName + ''' and SCHEMA_NAME(schema_id) = ''' + @owner + '''' + ')' + CHAR(10)
            set @SQLCommand += 'BEGIN' + CHAR(10)
            set @SQLCommand += 'CREATE TABLE ' + @Owner + '.' + @TableName + ' ('
            set @SQLCommand += 'LOGID BIGINT IDENTITY NOT NULL' + CHAR(10) + ',PARENTLOGID BIGINT NULL' + CHAR(10) + ',AUDITID BIGINT NOT NULL' + CHAR(10) + ',AUDITACTION CHAR(1) NOT NULL, LOGAPP VARCHAR(128), LOGUSER VARCHAR(128)'
            set @SQLCommand += @ListOfFields + CHAR(10)
            set @SQLCommand += ' PRIMARY KEY CLUSTERED(LOGID)) ON [' + @DataSpace + ']' + CASE WHEN @Compress = 1 THEN '  WITH (DATA_COMPRESSION = ROW)' ELSE '' END + CHAR(10)
            set @SQLCommand += 'END'
            exec (@SQLCommand)
            /*PRINT (@SQLCommand)*/
        SET @SqlCommand = 'SELECT @ObjectId = OBJECT_ID FROM SYS.TABLES WHERE NAME = ''' + @TableName + '''' + ' AND SCHEMA_ID = SCHEMA_ID(' + '''' + @Owner + '''' + ')';
        EXECUTE sp_executesql @SqlCommand, N'@ObjectId INT out', @ObjectId out
        /*set @SQLCommand =  'IF EXISTS (SELECT 1 FROM sys.tables WHERE name = ''' + @TableName + ''' and SCHEMA_NAME(schema_id) = ''' + @owner + '''' + ')' + CHAR(10)*/
        set @SQLCommand =  'IF NOT EXISTS (SELECT 1 FROM sys.tables t inner join sys.indexes i on t.object_id = i.object_id where t.name = ''' + @TableName + '''  AND t.schema_id = schema_id(''' + @Owner + ''') AND i.name in (''IX' +  CAST(@ObjectId AS VARCHAR) + '_LR''))' + CHAR(10)
        set @SQLCommand += 'BEGIN' + CHAR(10)
        set @SQLCommand += 'CREATE NONCLUSTERED INDEX IX' + CAST(@ObjectId AS VARCHAR) + '_LR ON ' + @Owner + '.' + @TableName + ' (LOGAPP, LOGUSER) ' + CASE WHEN @Compress = 1 THEN '  WITH (DATA_COMPRESSION = ROW)' ELSE '' END + ' ON [' + @IndexSpace + ']' + CHAR(10)
        set @SQLCommand += 'END'
        /*PRINT (@SQLCommand)*/
        exec (@SQLCommand)
        set @SQLCommand =  'IF NOT EXISTS (SELECT 1 FROM sys.tables t inner join sys.indexes i on t.object_id = i.object_id where t.name = ''' + @TableName + '''  AND t.schema_id = schema_id(''' + @Owner + ''') AND i.name in (''IX' + CAST(@ObjectId AS VARCHAR) + '_AA''))' + CHAR(10)
        set @SQLCommand += 'BEGIN' + CHAR(10)
        set @SQLCommand += 'CREATE NONCLUSTERED INDEX IX' + CAST(@ObjectId AS VARCHAR) + '_AA ON ' + @Owner + '.' + @TableName + ' (AUDITID, AUDITACTION) INCLUDE (LOGID, PARENTLOGID) ' + CASE WHEN @Compress = 1 THEN '  WITH (DATA_COMPRESSION = ROW)' ELSE '' END + ' ON [' + @IndexSpace + ']' + CHAR(10)
        set @SQLCommand += 'END'
        /*PRINT (@SQLCommand)*/
        exec (@SQLCommand)
        END
        /*Trunca tabelas de auditoria*/
        IF @AuditOperation = 'T'
        BEGIN
            set @SQLCommand =  'IF EXISTS (SELECT 1 FROM sys.tables WHERE name = ''' + @TableName + ''' and SCHEMA_NAME(schema_id) = ''' + @owner + '''' + ')' + CHAR(10)
            set @SqlCommand += 'TRUNCATE TABLE ' + @Owner + '.' + @TableName
            EXECUTE sp_executesql @SQLCommand
        END
        /*Exclui tabelas de auditoria*/
        IF @AuditOperation = 'D'
        BEGIN
            set @SQLCommand =  'IF EXISTS (SELECT 1 FROM sys.tables WHERE name = ''' + @TableName + ''' and SCHEMA_NAME(schema_id) = ''' + @owner + '''' + ')' + CHAR(10)
            set @SqlCommand += 'DROP TABLE ' + @Owner + '.' + @TableName
            EXECUTE sp_executesql @SQLCommand
        END
        /*Altera tipo de dados dos campos alterados na tabela original*/
        IF @AuditOperation IN ('CX','RX')
        BEGIN
        DECLARE TableCompression CURSOR READ_ONLY FOR
        SELECT ST.NAME, IX.NAME
        FROM SYS.PARTITIONS SP
        INNER JOIN SYS.TABLES ST ON ST.OBJECT_ID = SP.OBJECT_ID
        LEFT OUTER JOIN SYS.INDEXES IX ON SP.OBJECT_ID = IX.OBJECT_ID AND SP.INDEX_ID = IX.INDEX_ID
        WHERE SP.DATA_COMPRESSION = CASE WHEN @AuditOperation = 'RX' THEN 1 ELSE 0 END AND ST.schema_id = SCHEMA_ID(@Owner)
        ORDER BY ST.NAME, SP.INDEX_ID
        OPEN TableCompression
            FETCH NEXT FROM TableCompression
            INTO @TableCompressed, @IndexCompressed
            WHILE @@FETCH_STATUS = 0
            BEGIN
                SET @SQLCommand = 'ALTER INDEX ' + @IndexCompressed + ' ON ' + @Owner + '.' + @TableCompressed +  + ' REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = ' + CASE WHEN @AuditOperation = 'CX' THEN 'ROW)' ELSE 'NONE)' END
                /*PRINT @SQLCommand*/
                EXEC sp_executesql @SQLCommand
            FETCH Next FROM TableCompression
            INTO @TableCompressed, @IndexCompressed
            END
        CLOSE TableCompression
        DEALLOCATE TableCompression
        END