find invalid views

Question

find invalid views

Lowell

SSC Guru

Points: 323518
More actions
September 9, 2009 at 11:50 am

#156197

ok, supposed someone runs an upgrade script on a dev database, adding dropping tables, columns, what have you. typical scenario.
that scrip also ran sp_refreshview for every view, and that "someone" ignored any errors and closed the window, but then that "someone" came to you and said " i ran this script, but there were errors, i think related to views, i dunno. so you can't go back to his SSMS window and grab the results to manually go thru them;
after they are all refreshed, how can i find which views are invalid? i looked with select * from sys.views but see nothing related in invalid-ness; i would swear i knew this, but i'm dropping the ball today.
i guess i could run sp_refresh agaisnt each view again, because it would still be broken if a table got renamed, column dropped, etc, but it would be nioce to determine broken views/procs prgramatically.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

JamieX Hall of Fame Points: 3763 More actions · Answer 1

After reading your post I was thinking how many times I go to this one database I "look after but don't manage" and find tonnes of views that are broken. It's just a small DB, but worth a look. So I wrote a little something up to check it. Seems to work. It's a bit ghetto with detecting error in try catch and using a cursor, but if you want to try it out, it's up to you.

SET NOCOUNT ON
DECLARE @badViews TABLE (viewname nvarchar(4000))
DECLARE @tmpView NVARCHAR(4000)
DECLARE acursor CURSOR FOR
select QUOTENAME(s.name)+'.'+QUOTENAME(v.name) from sys.views v
inner join sys.schemas s on v.schema_id = s.schema_id
OPEN acursor
FETCH NEXT FROM acursor INTO @tmpView
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRANSACTION
BEGIN TRY
exec sp_refreshview @tmpView
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
INSERT @badViews VALUES (@tmpView)
END CATCH
FETCH NEXT FROM acursor INTO @tmpView
END
CLOSE acursor
DEALLOCATE acursor
SELECT * FROM @badViews

EDIT-Made a small change so it works on SQL 2008 as well

Lowell SSC Guru Points: 323518 More actions · Answer 2

Thanks Jamie; that's about how i would have tackled it too, but i thought there was some new sys views or the DMV Data Management Views that might identify items that were not compiling;

I found a CLR based example that basically does what our cursor would do...test each view and see if it fails.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!