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


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