>----- Original Message -----
>From: "Jamie Jackson"
>
>> I don't know if this is related, but if I've got a view (using *) that
>> depends on other views, and I alter a sub-view, I get column caching
>> even directly within enterprise manager. I put a utility in my app to
>> call a "view cache"-clearing sproc to rememedy this.
>
>Hm... These views do depend on sub-views. But, the views themselves aren't
>using *, just the cf queries calling the views. I'd still be interested in
>your view cache-clearing sproc, though.
Sorry, I didn't see your reply until now. This is the sproc I made to
clear views. Now that I look at it, it refreshes all the views on the
server, so you'll probably want to restrict it to a single database...
Jamie
CREATE PROC sp_RefreshAllUserViews
AS
DECLARE @ViewName varchar(255)
DECLARE ViewCursor CURSOR FOR
SELECT TABLE_NAME FROM information_schema.views
WHERE not table_name like 'sys%'
OPEN ViewCursor
FETCH NEXT FROM ViewCursor INTO @ViewName
WHILE @@fetch_status = 0
BEGIN
exec sp_refreshview @ViewName
PRINT 'Refreshed ' + @ViewName
FETCH NEXT FROM ViewCursor INTO @ViewName
END
CLOSE ViewCursor
DEALLOCATE ViewCursor
GO
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
