Testing for Broken Views
We recently found a view in our warehouse which had only been created for demonstration purposes but the underlying tables had changed over time. The view was broken and, of course, someone found it. This necessitated a remediation which would identify broken views and create a record of the issue. Ideally, there would never be broken views if this process was in place so we could also create a record showing how few issues we have had. At first, I thought of creating a SQL Agent job which would query the view and fail when the view was not working. But this seemed like a lot of work as views got added over time. So, something more flexible was called for. I wanted to create a procedure that would test all of the views in a database and record the relevant data if the view broke. I created a procedure with a try-catch block and selected the top(1) record from the views and when there was a failure the catch block would insert the record...