EXEMPLO_1212310 Database

Parameters

Name	Type	Mode

Definition

CREATE PROCEDURE p_ChangeFileLocation
/*Version=07.08.19*/
AS
DECLARE @tableName NVARCHAR(50),
        @tableId INT,
        @indexName NVARCHAR(50),
        @indexId INT,
        @isUnique BIT,
        @indexType NVARCHAR(50),
        @indexColumnId INT,
        @indexColumnName NVARCHAR(50),
        @maxColId INT,
        @DataSpace NVARCHAR(50),
        @IndexSpace NVARCHAR(50),
        @SchemaName NVARCHAR(50),
        @SQLCommand NVARCHAR(MAX)
EXEC P_AUDITLOG @AuditOperation = 'DT'
SELECT @SchemaName = AUDITSCHEMANAME, @DataSpace = DATASPACE, @IndexSpace = INDEXSPACE FROM ZAUDITCONFIG
DECLARE get_index CURSOR FOR
SELECT O.NAME, o.object_id, I.name, I.index_id
FROM SYS.objects O INNER JOIN
SYS.indexes I ON O.object_id = I.object_id
WHERE O.SCHEMA_ID=SCHEMA_ID(@SchemaName)
AND (O.NAME COLLATE SQL_Latin1_General_CP1_CI_AI IN (SELECT OBJECTNAME COLLATE SQL_Latin1_General_CP1_CI_AI FROM ZAUDITITEMS) OR O.NAME COLLATE SQL_Latin1_General_CP1_CI_AI IN ('ZAUDITCHANGES','ZAUDITSCHEMAEVENTS'))
OPEN get_index
FETCH NEXT FROM get_index
INTO @tableName, @tableId, @indexName, @indexId
WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE mount_index CURSOR FOR
    SELECT i.is_unique, i.type_desc, ic.index_column_id, c.name
    FROM SYS.indexes I INNER JOIN
    SYS.index_columns IC ON IC.index_id = I.index_id AND IC.object_id = I.object_id INNER JOIN
    SYS.columns C ON C.object_id = I.object_id AND C.column_id = IC.column_id
    WHERE i.index_id = @indexId and i.object_id = @tableId
    ORDER BY IC.index_column_id
    OPEN mount_index
    FETCH NEXT FROM mount_index
    INTO @isUnique, @indexType, @indexColumnId, @indexColumnName
    WHILE @@FETCH_STATUS = 0
    BEGIN
    SELECT  @maxColId = MAX(index_column_id) FROM SYS.index_columns WHERE object_id = @tableId AND index_id = @indexId
    IF @indexColumnId = 1
    SET @SQLCommand = 'CREATE ' + CASE WHEN @isUnique = 1 THEN 'UNIQUE ' ELSE '' END +
                        @indexType + ' INDEX ' +
                        @indexName +
                        ' ON ' + @SchemaName + '.' +
                        @tableName +
                        '(' + @indexColumnName
    ELSE
    SET @SQLCommand += ',' + @indexColumnName
    IF @indexColumnId = @maxColId
    SET @SQLCommand += ') WITH DROP_EXISTING ON [' + CASE WHEN @indexType = 'CLUSTERED' THEN @DataSpace ELSE @IndexSpace END + ']'
    FETCH NEXT FROM mount_index
    INTO @isUnique, @indexType, @indexColumnId,@indexColumnName
    END
    /*PRINT @SQLCOMMAND*/
    EXEC sp_executesql @SQLCOMMAND
    CLOSE mount_index;
    DEALLOCATE mount_index;
FETCH NEXT FROM get_index
INTO @tableName, @tableId, @indexName, @indexId
END
CLOSE get_index
DEALLOCATE get_index
EXEC P_AUDITLOG @AuditOperation = 'C'