Posted By

DaveChild on 09/11/08


Tagged

sanitychecks


Versions (?)

Who likes this?

3 people have marked this snippet as a favorite

Plastyksouljah
nublaii
rhainek


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.

  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  

You need to login to post a comment.