Catch errors in views without execute

Eliran 0
Viewed 38

I try to pull a list of views with their errors, without executing them.

Here is an example in the code below:

create view vw_first as
select id, first_name, last_name
from dim_employees

create view vw_second as
select id, first_name
from dim_employees

create view vw_third as 
select b.id, a.first_name, last_name -- no prefix of table alias
from vw_first a
     join vw_second b on a.id = b.id

--- Here the issue begin

alter view vw_second as 
select id, first_name, last_name
from dim_employees


--- Now, when I run the vw_third view I will get Ambiguous column error.

select *
from vw_third

For this situation, I need a monitor or a way to get a list of those views that will cause errors.

I familiar with sp_viewrefresh procedure, but it runs and locks the views and the tables within them.

I need a way to find them without executing them.

sql-server view sql
1 Answers

You should use a mechanism that prevent you from altering view which is used in another object. Fortunately SQL Server implemented this feature with adding WITH SCHEMABINDING in definition of view.

For example when you create or alter third view like this:

create view vw_third
WITH SCHEMABINDING
as
select b.id, a.first_name, last_name -- no prefix of table alias
from vw_first a
     join vw_second b on a.id = b.id

SQL Server prevent you from altering first and second view. So you will inform that the view you are altering is used in third view.

Meyssam Toluie0
Related