Posted by rod.weir on 03/30/06 06:07

Hello, I have the following code to iterate through each view in a SQL
Server and call the "sp_refreshview" command against it. It works
great until it finds a view that is damaged, or otherwise cannot be
refreshed. Then the whole routine stops working.

Can someone please help me re-write this code so that any views that
fail the "sp_refreshview" command get skipped. I'm sure it's just a
matter of putting some basic error trapping into the loop, but I've had
a few goes at it and failed.

Many thanks.


DECLARE @DatabaseObject varchar(255)
DECLARE ObjectCursor CURSOR
FOR SELECT table_name FROM information_schema.tables WHERE table_type =
'view'
OPEN ObjectCursor
FETCH NEXT FROM ObjectCursor INTO @DatabaseObject
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC sp_refreshview @DatabaseObject
Print @DatabaseObject + ' was successfully refreshed.'
FETCH NEXT FROM ObjectCursor INTO @DatabaseObject
END
CLOSE ObjectCursor
DEALLOCATE ObjectCursor
GO

• Next in forum: Re: SQL Server 2005: Collation Conflict Error when selecting Database Properties
• Prev in forum: Sqlserver Login and user
• Thread view: I'm sure this is an easy one...Error trap to skip over a "bad" object.

[Reply to this message]