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!

• JamieX

  Hall of Fame

  Points: 3763

  More actions

  September 10, 2009 at 8:06 am

  #1051720

  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

  September 10, 2009 at 9:14 am

  #1051781

  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!