Pamela Mooney shows us how we can find broken code on our SQL Server instances:
Before we approached our last major SQL Server upgrade, I was curious about what might break. Yes, I had used the DEA to check our code against deprecated or discontinued code. But I am talking about code that might not have been used in some time and would break because objects no longer existed, or other things like that. So I wrote these scripts to refresh the sprocs, views and functions in our (non-production) environment.
Pamela’s motivation was to handle code which breaks during an update. You can also use this to see what you can probably deprecate—if the view doesn’t work, it can’t be in use. That means either someone should fix it or drop it.