We Recommend

SQL Cookbook
Written in O'Reilly's popular Problem/Solution/Discussion style, the SQL Cookbook is sure to please. Anthony's credo is: "When it comes down to it, we all go to work, we all have bills to pay, and we all want to go home at a reasonable time and enjoy what's still available of our days." The SQL Cookbook moves quickly from problem to solution, saving you time each step of the way.

Posted By

DaveChild on 09/11/08

Tagged

sanitychecks

Versions (?)

Who likes this?

2 people have marked this snippet as a favorite

Plastyksouljah
nublaii

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.

Embed | Plain 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
 
GO

Report this snippet

Comment:

You need to login to post a comment.