June 7, 2016 at 9:19 am
Hi,
I have found few refreshall Store procedures,but sp only refreshing dbo owned schemas, but my requirement is refresh all views irrespective of what schemas they are in DB. I have full access to DB. Could you tell me where I am missing.
Many Thanks
Eg Script:
CREATE PROCEDURE dbo.RefreshAllViews AS
-- This sp will refresh all views in the catalog.
-- It enumerates all views, and runs sp_refreshview for each of them
DECLARE abc CURSOR FOR
SELECT TABLE_NAME AS ViewName
FROM INFORMATION_SCHEMA.VIEWS
OPEN abc
DECLARE @ViewName varchar(128)
-- Build select string
DECLARE @SQLString nvarchar(2048)
FETCH NEXT FROM abc
INTO @ViewName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQLString = 'EXECUTE sp_RefreshView '+@ViewName
PRINT @SQLString
EXECUTE sp_ExecuteSQL @SQLString
FETCH NEXT FROM abc
INTO @ViewName
END
CLOSE abc
DEALLOCATE abc
June 7, 2016 at 9:24 am
i've done something similar.
i use sys.views and not information schemas; there's a LOT of information missing in those compatiblity views, so i avoid them like the plague.
select
'exec sp_refreshview '
+ quotename(schema_name(schema_id))
+ '.'
+ quotename(name) + ';'
from sys.views
the other thing to consider is invalid views....if base tables go missing(renamed / dropped) , or the columns in them change name/dropped, potentially a command int he cursor could fail;
I have added a try...catch block to log the failed refreshes.
Lowell
June 8, 2016 at 2:14 am
Hi Lowell,
Could you share your script.
Thank you
Raj
June 8, 2016 at 4:36 am
Sure, its part of a scripti share here that checks for all invalid objects. It triesto recompile procs, functions and views.
Lowell
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply