Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
strauss_jon
Starting Member
23 Posts |
 Posted - 2011-07-12 : 09:29:32
|
| Hi there, I thought this would be really simple but I can't find out how.I need a script to list the broken objects (views, sp’s etc) which may have invalidated due to changes in the schema. In oracle there's a field in the views sys table which flags its state, i can't find an equivalent in sql server. Is there one? Searched high and low on google but can't find anything useful. I could just loop through all the views, select from them and if they error, then they’re broken. But that would take forever to run as some of the views are complex and do take a long time.Any ideas? Thanks in advance, Jon |
|
|
vaari
Starting Member
15 Posts |
Posted - 2011-07-12 : 10:49:21
|
| I don't know if there's any flag in SQL Server for this but a couple of options based on what you're trying to do:a) Script out and try to re-create the views/SPs in another DB (that has all the underlying tables) - as long as you're trying to find out issues where columns that don't exist in tables anymore are being accessed you'll get an error when creating the view/SP. This will not work if you're accessing tables that don't exist anymore - it will create the object due to deferred name resolutionb) Loop through each view with an invalid WHERE clause...for e.g.SELECT * FROM MyView WHERE 1=2This should not take any time to execute and will let you know if there are any errors. It is pretty simple to generate SELECT this for all views programatically |
 |
|
|
strauss_jon
Starting Member
23 Posts |
|
|
|
|
|
How to list invalid / broken objects (views / sps)