CODEWITHSUNDEEP
sqlsql-serversql-server-2008viewruntime-error
Doctor Coder
Doctor Coder

Reputation: 1061

SQL Server 2008 r2: How to check all views for runtime errors?

I have a LOT of views in the database. Each view ofc refers to one or more tables. There was some work done with those tables (alter, delete columns) and now i need to check all views for any runtime errors.

I went straithforward: got list of all views, iterated over it and launch SELECT TOP 0 * FROM view_name dynamically so any errors should appear in the Messages pane.

This is my code

DECLARE @view_name_template varchar(max) = '%'
DECLARE @columnList varchar(75) = '*'
--------------------------
DECLARE @tmp_views AS TABLE (view_name varchar(max))
DECLARE @view_name varchar(max)
DECLARE @sqlCommand nvarchar(max)
DECLARE @num int = 1
DECLARE @total_count int

SET NOCOUNT ON

INSERT INTO @tmp_views 
SELECT name FROM sys.views
WHERE name LIKE @view_name_template

SELECT @total_count = COUNT(*) FROM sys.views WHERE name LIKE @view_name_template

DECLARE db_cursor CURSOR FOR  
    SELECT view_name FROM @tmp_views ORDER BY LOWER(view_name)

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @view_name

WHILE @@FETCH_STATUS = 0  
BEGIN  
       SET @sqlCommand = 'SELECT TOP 0 ' + @columnList + ' FROM ' + @view_name
       PRINT CAST(@num as varchar(31)) + '/' + CAST(@total_count as varchar(31)) + ' ' + @sqlCommand

       EXECUTE sp_executesql @sqlCommand

       FETCH NEXT FROM db_cursor INTO @view_name         
       SET @num = @num + 1
END  

CLOSE db_cursor  
DEALLOCATE db_cursor

It works fine except it completely freezes on some views (select from those views in other window works extremely fast and fine). I think it is server a memory overflow issue or something similar.

Tell me please: what is the lightweighiest way to check view has errors or not? Maybe SQL Server has a special function or stored procedure?

Upvotes: 3

Views: 2269

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

The code is not "hanging". It is waiting for the view to run, despite the top 0.

SQL Server offers several ways of testing queries. In addition to the top 0, you also have:

  • `set parseonly1
  • set noexec on

And then the more recent sp_describe_first_result_set.

Each of these do different things. parseonly checks for syntax errors but doesn't look at table layouts. I believe noexec completely compiles the query, creating the execution plan. top 0 will compile the query and also run it.

In some cases, the optimizer may not recognize that a query that returns no rows might need to do no work. For instance, there might be subqueries that are run despite the top 0, and this is causing the delay.

Two approaches. The first is to use noexec on (documented here). The second, if feasible, would be to create another database with the same structure and no data. You can then test the queries on that database.

Upvotes: 1

Related Questions