EXEMPLO_1212310 Database

Parameters

Name	Type	Mode
@Owner	nvarchar	IN
@AuditOperation	nvarchar	IN
@DataSpaceIn	nvarchar	IN
@IndexSpaceIn	nvarchar	IN
@Compression	int	IN

Definition

CREATE PROCEDURE p_CreateAuditConfigTables
(
/*Version=07.08.19*/
@Owner NVARCHAR(38) = 'TOTVSAUDIT',
@AuditOperation NVARCHAR(38) = NULL, /* CC=CREATE CONFIG TABLE, DC=DROP CONFIG TABLE, CA=CREATE AUDIT TABLES, CD=DROP AUDIT TABLES */
@DataSpaceIn NVARCHAR(30) = 'PRIMARY',
@IndexSpaceIn NVARCHAR(30) = 'PRIMARY',
@Compression int = 0
)
AS
DECLARE @CurrentSchema VARCHAR(30) = schema_name(),
        @DataSpace VARCHAR(30) = @DataSpaceIn,
        @IndexSpace VARCHAR(30) = @IndexSpaceIn,
        @SQLCommand NVARCHAR(MAX),
        @Compress bit = CASE WHEN REPLACE(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR),'.','') < '140000000' THEN 0 ELSE 1 END
IF @AuditOperation = 'CA' /* Cria tabelas de auditoria de contexto e ddl */
BEGIN
SELECT @DataSpace = DATASPACE, @IndexSpace = INDEXSPACE FROM ZAUDITCONFIG;
/* Se o parametro @Compression = 0 não faz compression da tabela*/
SET @Compress = @Compress * @Compression
IF NOT EXISTS (SELECT 1 FROM SYS.SCHEMAS WHERE NAME = @OWNER)
BEGIN
    EXEC ('CREATE SCHEMA [' + @owner + '] AUTHORIZATION [' + @CurrentSchema + ']')
    EXEC ('CREATE ROLE [TOTVSAUDIT_Role] AUTHORIZATION [' + @CurrentSchema + ']')
    EXEC ('GRANT ALTER ON SCHEMA::[' + @owner + '] TO [TOTVSAUDIT_Role]')
    EXEC ('GRANT CONTROL ON SCHEMA::[' + @owner + '] TO [TOTVSAUDIT_Role]')
    EXEC ('GRANT SELECT, DELETE, INSERT, ALTER ON SCHEMA::[' + @owner + '] TO [TOTVSAUDIT_Role]')
    EXEC ('GRANT SELECT ON SCHEMA::[' + @CurrentSchema + '] TO [TOTVSAUDIT_Role]')
END
IF NOT EXISTS (SELECT 1 FROM SYS.tables WHERE name = 'ZAUDITCHANGES' AND SCHEMA_NAME(schema_id) = @Owner)
BEGIN
/* =====================================================================================================================*/
/* ZAUDITCONFIG ::  Contem as informações sobre o status do log de auditoria, bem como do nome do schema que o          */
/*                  armazena.                                                                                           */
/* =====================================================================================================================*/
SET @SQLCommand ='CREATE TABLE ' + @owner + '.ZAUDITCHANGES' + CHAR(10)
SET @SQLCommand += '    (' + CHAR(10)
SET @SQLCommand += '      AUDITID bigint identity(1,1) not null,' + CHAR(10)
SET @SQLCommand += '      AUDITDATE DATETIME2 not null,' + CHAR(10)
SET @SQLCommand += '      USERNAME nvarchar(60) COLLATE SQL_Latin1_General_CP1_CI_AS null,' + CHAR(10)
SET @SQLCommand += '      LOGINNAME nvarchar(60) COLLATE SQL_Latin1_General_CP1_CI_AS null,' + CHAR(10)
SET @SQLCommand += '      HOSTNAME nvarchar(128) COLLATE SQL_Latin1_General_CP1_CI_AS null,' + CHAR(10)
SET @SQLCommand += '      APPLICATIONNAME nvarchar(128) COLLATE SQL_Latin1_General_CP1_CI_AS null,' + CHAR(10)
SET @SQLCommand += '      APPLICATIONMODULE nvarchar(128) COLLATE SQL_Latin1_General_CP1_CI_AS null,' + CHAR(10)
SET @SQLCommand += '      MACHINEIP nvarchar(45) COLLATE SQL_Latin1_General_CP1_CI_AS null,' + CHAR(10)
SET @SQLCommand += '      SESSIONID nvarchar(45) COLLATE SQL_Latin1_General_CP1_CI_AS null,' + CHAR(10)
SET @SQLCommand += '      AUDITACTION char(1) COLLATE SQL_Latin1_General_CP1_CI_AS not null,' + CHAR(10)
SET @SQLCommand += '      RECORDSAFFECTED int null,' + CHAR(10)
SET @SQLCommand += '      AUDITOBJECTNAME nvarchar(30),' + CHAR(10)
SET @SQLCommand += '      DMLTEXT text null,' + CHAR(10)
SET @SQLCommand += '      CONSTRAINT PKZAUDITCHANGES PRIMARY KEY (AUDITID) ON [' + @IndexSpace + ']' + CHAR(10)
SET @SQLCommand += '    ) ON [' + @DataSpace + ']' + CASE WHEN @Compress = 1 THEN '  WITH (DATA_COMPRESSION = ROW)' ELSE '' END + CHAR(10)
/*PRINT @SQLCommand*/
EXEC sp_executesql @SQLCommand
SET @SQLCommand ='CREATE NONCLUSTERED INDEX IXZAUDITCHANGES_OBJECTNAME ON ' + @owner + '.ZAUDITCHANGES (AUDITOBJECTNAME) INCLUDE (AUDITID) ' + CASE WHEN @Compress = 1 THEN '  WITH (DATA_COMPRESSION = ROW)' ELSE '' END + ' ON [' + @IndexSpace + ']'
/*PRINT @SQLCommand*/
EXEC sp_executesql @SQLCommand
SET @SQLCommand ='CREATE NONCLUSTERED INDEX IXZAUDITCHANGES_AUDITDATE ON ' + @owner + '.ZAUDITCHANGES (AUDITDATE) INCLUDE (AUDITID, AUDITACTION) ' + CASE WHEN @Compress = 1 THEN '  WITH (DATA_COMPRESSION = ROW)' ELSE '' END + ' ON [' + @IndexSpace + ']'
/*PRINT @SQLCommand*/
EXEC sp_executesql @SQLCommand
END
IF NOT EXISTS (SELECT 1 FROM SYS.tables WHERE name = 'ZAUDITSCHEMAEVENTS' AND SCHEMA_NAME(schema_id) = @Owner)
BEGIN
SET @SQLCommand ='CREATE TABLE ' + @owner + '.ZAUDITSCHEMAEVENTS' + CHAR(10)
SET @SQLCommand += '    (' + CHAR(10)
SET @SQLCommand += '    AUDITID BIGINT IDENTITY(1,1) NOT NULL,' + CHAR(10)
SET @SQLCommand += '    USERNAME nvarchar(60) COLLATE SQL_Latin1_General_CP1_CI_AS null,' + CHAR(10)
SET @SQLCommand += '    LOGINNAME nvarchar(60) COLLATE SQL_Latin1_General_CP1_CI_AS null,' + CHAR(10)
SET @SQLCommand += '    HOSTNAME nvarchar(128) COLLATE SQL_Latin1_General_CP1_CI_AS null,' + CHAR(10)
SET @SQLCommand += '    APPLICATIONNAME nvarchar(128) COLLATE SQL_Latin1_General_CP1_CI_AS null,' + CHAR(10)
SET @SQLCommand += '    APPLICATIONMODULE nvarchar(128) COLLATE SQL_Latin1_General_CP1_CI_AS null,' + CHAR(10)
SET @SQLCommand += '    MACHINEIP nvarchar(45) COLLATE SQL_Latin1_General_CP1_CI_AS null,' + CHAR(10)
SET @SQLCommand += '    SESSIONID nvarchar(45) COLLATE SQL_Latin1_General_CP1_CI_AS null,' + CHAR(10)
SET @SQLCommand += '    EVENTDATE   DATETIME2 NULL DEFAULT CURRENT_TIMESTAMP,' + CHAR(10)
SET @SQLCommand += '    EVENTTYPE   NVARCHAR(64) NULL,' + CHAR(10)
SET @SQLCommand += '    [STATEMENT] NVARCHAR(MAX) NULL,' + CHAR(10)
SET @SQLCommand += '    SCHEMANAME NVARCHAR(256) NULL,' + CHAR(10)
SET @SQLCommand += '    OBJECTNAME NVARCHAR(256) NULL,' + CHAR(10)
SET @SQLCommand += '    CONSTRAINT PKZAUDITSCHEMAEVENTS PRIMARY KEY (AUDITID) ON [' + @IndexSpace + ']' + CHAR(10)
SET @SQLCommand += '    ) ON [' + @DataSpace + ']' + CASE WHEN @Compress = 1 THEN '  WITH (DATA_COMPRESSION = ROW)' ELSE '' END + CHAR(10)
/*PRINT @SQLCommand*/
EXEC sp_executesql @SQLCommand
END
END
IF @AuditOperation = 'DA' /* Drop as tabelas de autoria de contexto e ddl */
BEGIN
    IF EXISTS (SELECT 1 FROM SYS.TRIGGERS WHERE NAME = 't_DDLAudit')
    BEGIN
    SET @SQLCommand = 'EXEC p_DDLTriggerGenerator ''D'''
    EXEC sp_executesql @SQLCommand
    END
    IF EXISTS (SELECT 1 FROM SYS.tables WHERE name = 'ZAUDITCHANGES' AND SCHEMA_NAME(schema_id) = @Owner)
    BEGIN
    SET @SQLCommand =('DROP TABLE ' + @Owner + '.ZAUDITCHANGES')
    /*PRINT @SQLCommand*/
    EXEC sp_executesql @SQLCommand
    END
    IF EXISTS (SELECT 1 FROM SYS.tables WHERE name = 'ZAUDITSCHEMAEVENTS' AND SCHEMA_NAME(schema_id) = @Owner)
    BEGIN
    SET @SQLCommand =('DROP TABLE ' + @Owner + '.ZAUDITSCHEMAEVENTS')
    /*PRINT @SQLCommand*/
    EXEC sp_executesql @SQLCommand
    END
END
IF @AuditOperation = 'TA' /* Drop as tabelas de autoria de contexto e ddl */
BEGIN
    IF EXISTS (SELECT 1 FROM SYS.TRIGGERS WHERE NAME = 't_DDLAudit')
    BEGIN
    SET @SQLCommand = 'EXEC p_DDLTriggerGenerator ''D'''
    EXEC sp_executesql @SQLCommand
    END
    IF EXISTS (SELECT 1 FROM SYS.tables WHERE name = 'ZAUDITCHANGES' AND SCHEMA_NAME(schema_id) = @Owner)
    BEGIN
    SET @SQLCommand =('TRUNCATE TABLE ' + @Owner + '.ZAUDITCHANGES')
    /*PRINT @SQLCommand*/
    EXEC sp_executesql @SQLCommand
    END
    IF EXISTS (SELECT 1 FROM SYS.tables WHERE name = 'ZAUDITSCHEMAEVENTS' AND SCHEMA_NAME(schema_id) = @Owner)
    BEGIN
    SET @SQLCommand =('TRUNCATE TABLE ' + @Owner + '.ZAUDITSCHEMAEVENTS')
    /*PRINT @SQLCommand*/
    EXEC sp_executesql @SQLCommand
    END
END