Refreshing all Views after a schema change
After a schema change in a SQL Server database, if you have any views that contain a SELECT * statement in them referring to a changed table, you're almost certainly going to get gibberish (at best!) out of those views.
The following stored procedure (which you could just as easily run as inline SQL) works a treat for fixing these up:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GOIF EXISTS (SELECT 1 FROM sysobjects WHERE name = 'Util_RefreshAllViews' AND XType = 'P')
BEGIN
DROP PROCEDURE Util_RefreshAllViews
ENDGO
CREATE PROCEDURE Util_RefreshAllViews
AS
BEGIN
SET NOCOUNT ONDECLARE @tabViews TABLE
(
ViewName SYSNAME,
Refreshed INT DEFAULT (0)
)
DECLARE @vViewName SYSNAMEINSERT
INTO @tabViews
(
ViewName
)
SELECT Name
FROM sysobjects
WHERE xtype = 'v'
AND OBJECTPROPERTY(id, 'IsMSShipped') = 0WHILE EXISTS (SELECT 1 FROM @tabViews WHERE Refreshed = 0)
BEGIN
SELECT @vViewName = ViewName
FROM @tabViews
WHERE Refreshed = 0EXEC sp_refreshview @vViewName
PRINT 'Refreshed: ' + @vViewName
UPDATE @tabViews
SET Refreshed = -1
WHERE ViewName = @vViewName
ENDEND
GO
