Post reply

Refresh All views in Database including all SchemasViews

  • June 7, 2016 at 9:19 am

    #326853

    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

    #1883644

    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

    --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!

  • June 8, 2016 at 2:14 am

    #1883805

    Hi Lowell,

    Could you share your script.

    Thank you

    Raj

  • June 8, 2016 at 4:36 am

    #1883825

    Sure, its part of a scripti share here that checks for all invalid objects. It triesto recompile procs, functions and views.

    sp_InvalidObects.txt

    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 4 posts - 1 through 3 (of 3 total)

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