Refresh all views - Aggiornare tutte le viste logiche

SET NOCOUNT ON;

DECLARE @tblTmpViews TABLE (view_name varchar(100));

DECLARE @view_name nvarchar(100);

INSERT @tblTmpViews SELECT [name] FROM sysobjects WHERE xtype='V';

DELETE @tblTmpViews WHERE view_name IN ('tax_OrfaniSanguinettiVP_vl', 'tax_OrfaniSanguinettiFS_vl', 'tax_OrfaniSanguinettiVU_vl');

DELETE @tblTmpViews WHERE view_name LIKE '%ven_RicercaContratti_vl%';

DECLARE crsrViews CURSOR FOR SELECT view_name FROM @tblTmpViews ORDER BY view_name;

OPEN crsrViews;

FETCH NEXT FROM crsrViews INTO @view_name;

WHILE @@FETCH_STATUS = 0 BEGIN PRINT 'Refreshing view ' + @view_name + '...' EXEC sp_refreshview @view_name FETCH NEXT FROM crsrViews INTO @view_name END CLOSE crsrViews;

DEALLOCATE crsrViews;

PRINT '';

PRINT 'Refresh done successfully.';

SET NOCOUNT OFF;

---

Versione 2: con filtro, su una sola riga:

SET NOCOUNT ON; DECLARE @tblTmpViews TABLE (view_name varchar(100)); DECLARE @view_name nvarchar(100); INSERT @tblTmpViews SELECT [name] FROM sysobjects WHERE xtype='V'; DELETE @tblTmpViews WHERE view_name IN ('tax_OrfaniSanguinettiVP_vl', 'tax_OrfaniSanguinettiFS_vl', 'tax_OrfaniSanguinettiVU_vl'); DELETE @tblTmpViews WHERE view_name LIKE '%ven_RicercaContratti_vl%'; DECLARE crsrViews CURSOR FOR SELECT view_name FROM @tblTmpViews ORDER BY view_name; OPEN crsrViews; FETCH NEXT FROM crsrViews INTO @view_name; WHILE @@FETCH_STATUS = 0 BEGIN PRINT 'Refreshing view ' + @view_name + '...' EXEC sp_refreshview @view_name FETCH NEXT FROM crsrViews INTO @view_name END CLOSE crsrViews; DEALLOCATE crsrViews; PRINT ''; PRINT 'Refresh done successfully.'; SET NOCOUNT OFF;