Posted By

DaveChild on 09/11/08

Tagged

Versions (?)

Last Edited at 09/11/08 10:52am

Statistics

Viewed 292 times

Favorited by 0 user(s)

Related snippets

SQL Run Sanity Checks SP

/ Published in: SQL

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.

Highlighted TextExpand

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

Report this snippet Tweet

Comments

Subscribe to comments

Comment:

You need to login to post a comment.