Advanced Search

SQL Run Sanity Checks SP

/ Published in: SQL
Save to your folder(s)
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.

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  1. CREATE TABLE [dbo].[sanity_check_results] (
  2. 	[intID] [INT] IDENTITY (1, 1) NOT NULL ,
  3. 	[ViewName] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
  4. 	[ProblemRows] [INT] NULL ,
  5. 	[SQLViewName] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL 
  6. )
  7.  
  8. CREATE PROCEDURE RunSanityChecks AS
  9.  
  10.     DECLARE @strViewName VARCHAR(100)
  11.     DECLARE @strSQL VARCHAR(500)
  12.  
  13.     TRUNCATE TABLE sanity_check_results
  14.  
  15.     DECLARE ViewCursor CURSOR READ_ONLY
  16.         FOR
  17.             SELECT TABLE_NAME FROM Information_Schema.Views WHERE TABLE_NAME LIKE 'sanity_check%'
  18.  
  19.         OPEN ViewCursor
  20.  
  21.         FETCH NEXT FROM ViewCursor
  22.         INTO @strViewName
  23.  
  24.         WHILE @@FETCH_STATUS = 0
  25.         BEGIN
  26.  
  27.             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
  28.             EXEC(@strSQL)
  29.  
  30.             FETCH NEXT FROM ViewCursor
  31.             INTO @strViewName
  32.  
  33.         END
  34.  
  35.     CLOSE ViewCursor
  36.     DEALLOCATE ViewCursor
  37.  
  38. GO

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.