Hi Lester,

Just try to "drop and recreate" your view if it is a dependency of
another view and tell me how far you get :)

Of course our schemas are documented but because they are based on
real world data the ideal schema does in fact change as customer
requirements are changed or even as the requirements are better
understood.

I had a view that selected the records of interest in a table. There
were also historical, deleted, and inactive records in the table,
hence I assumed the view would be a clean way to handle it.

I then created a view for a report that joined to that view as well as
several other tables, and this worked fine.

An additional flag was added to the table that the original view was
based on for a new requirement, and the view just needed to be
modified to test that flag. In a nutshell, I needed to add another
line to its where clause (not a tall ask).

The SQL script I generated was over 300 lines because I had to pretty
much drop and recreate about 15 views and 5 stored procedures in
total. It would have been a very simple "alter view" statement, but
instead it was an absolute PITA.

But I want to make two points.

1. Views still have a purpose, because unlike selectable stored
procedures the indices on the underlying table(s) can still be used.

2. NEVER ever ever use views inside another view or a stored procedure.

The only exception is if you can guarantee that your schema
requirements will not change in time, which like most developers, I do
not have that luxury.

Adam