CREATE TABLE [dbo].[sanity_check_results] (
	[intID] [int] IDENTITY (1, 1) NOT NULL ,
	[ViewName] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
	[ProblemRows] [int] NULL ,
	[SQLViewName] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL 
)

CREATE PROCEDURE RunSanityChecks AS

    DECLARE @strViewName varchar(100)
    DECLARE @strSQL varchar(500)

    TRUNCATE TABLE sanity_check_results
        
    DECLARE ViewCursor CURSOR READ_ONLY
        FOR
            SELECT table_name FROM Information_Schema.Views WHERE table_name LIKE 'sanity_check%'

        OPEN ViewCursor

        FETCH NEXT FROM ViewCursor
        INTO @strViewName

        WHILE @@FETCH_STATUS = 0
        BEGIN

            set @strSQL = 'INSERT INTO sanity_check_results (ViewName, ProblemRows, SQLViewName) SELECT replace(''' + @strViewName + ''', ''sanity_check_'', '''') AS ViewName, count(*) AS ProblemRows, ''' + @strViewName + ''' as SQLViewName from ' + @strViewName
            exec(@strSQL)
        
            FETCH NEXT FROM ViewCursor
            INTO @strViewName
        
        END

    CLOSE ViewCursor
    DEALLOCATE ViewCursor

GO

Finds and runs all views that start with "sanity\_check". Writes number of rows returned by each view to sanity\_check\_results table for use later. 

Includes SQL to generate results table.

SQL Run Sanity Checks SP

SQL