EXEMPLO_1212310 Database

Parameters

Name	Type	Mode

Definition

CREATE PROCEDURE SP_RECOMPILAVIEW AS
DECLARE @TABELA VARCHAR(100)
DECLARE @SQL VARCHAR(100)
DECLARE @VIEWERRO VARCHAR(100)
DECLARE @TEXTERRO VARCHAR(100)
DECLARE @CONT SMALLINT
DECLARE @CONTERRO SMALLINT
SET @SQL = ''
SET @CONT = 0
SET @CONTERRO = 0
SET @TEXTERRO = ''
DECLARE CUR_ALTERTABLE CURSOR
FOR SELECT OBJECT FROM GLIBRARY WHERE OBJECTTYPE = 'V'
OPEN CUR_ALTERTABLE
FETCH NEXT FROM CUR_ALTERTABLE INTO @TABELA
WHILE (@@FETCH_STATUS = 0 )
BEGIN
SET @SQL = 'EXEC sp_refreshview ' + '''' + @TABELA + ''''
BEGIN TRAN
BEGIN TRY
EXECUTE (@SQL)
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
SET @CONTERRO = @CONTERRO+1
SET @CONT = @CONT+1
IF @CONT = 1
SET @TEXTERRO = @TEXTERRO + @TABELA
ELSE
SET @TEXTERRO = @TEXTERRO + ',' + @TABELA
END CATCH
SET @VIEWERRO = 'A(s) view(s): ' + @TEXTERRO + ' não pode(m) ser recompilada(s). Favor verificar sua estrutura.'
FETCH NEXT FROM CUR_ALTERTABLE INTO @TABELA
SET @SQL = ''
END
IF @CONTERRO > 0
RAISERROR(@VIEWERRO , 16, 1)
CLOSE CUR_ALTERTABLE
DEALLOCATE CUR_ALTERTABLE