Test All SQL Views

gistfile1.sql

ALTER PROCEDURE [dbo].[spTestAllViews]
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
	DECLARE @Loopid int, @MaxID int, @ViewName varchar(100)

	SELECT quotename(table_schema) +'.' + quotename(table_name) as ViewName, identity(int,1,1) as ID
		INTO #test
		FROM information_schema.tables
		WHERE table_type = 'view'

	SELECT @LoopID =1,@MaxID =MAX(id) 
		FROM #test

	WHILE @LoopID <= @MaxID
	BEGIN

		SELECT @ViewName = ViewNAme 
			FROM #test
			WHERE id = @LoopID

		EXEC ('SELECT TOP 1 * FROM ' + @ViewName)
		SET @LoopID = @LoopID + 1
	END

	DROP TABLE #test

END

GO