Summary:
This will list all of the views on the database with a column highlighting if they are working correctly or not. Views that are populated with ‘Bad’ will need to be dropped or refreshed. Please also be aware that some views are dependent on other views. Once you have fixed the issue with the views run the script again 




Resolution:
Run following script:

SET NOCOUNT ON 
DECLARE @BadViews TABLE (View_Name varchar(255),View_Status varchar(255)) 
DECLARE @View_Name TABLE (rownum int IDENTITY (1,1)Primary key not null, View_Name varchar(255)) 
DECLARE @row_id bigint 
DECLARE @row_count bigint 
DECLARE @current_view varchar (255) 
SET @row_id = 1 

INSERT INTO @View_Name 
SELECT TABLE_NAME FROM information_schema.VIEWS 

SELECT  @row_count = count(*) FROM @View_Name 

WHILE @row_id <= @row_count 
begin 
SELECT @current_view = View_Name FROM @View_Name WHERE rownum = @row_id 

BEGIN TRY 
   BEGIN TRAN 
   exec sp_refreshview @current_view 
   COMMIT TRAN 
   INSERT INTO @BadViews (View_Name,View_Status) VALUES (@current_view,'Ok') 
   
END TRY 
BEGIN CATCH 
ROLLBACK TRAN 
    INSERT INTO @BadViews (View_Name,View_Status) VALUES (@current_view,'Bad') 
END CATCH 



SET @row_id = @row_id +1 
end 

SELECT View_Name,View_Status FROM @BadViews order by View_Status


Author:
Andre Atherley