Home > SQL Server > Refresh All Views in Database with T-SQL

Refresh All Views in Database with T-SQL

This is a handy little piece of code I wrote, which can help if you ever need to refresh your database views within SQL Server. I have tested this on SQL Server 2005 recently. I have also had a SQL Job run this stored procedure to update my views more frequently.  I had used this to keep views up-to-date for database integration projects.

   1: CREATE Procedure dbo.RefreshAllViews
   2: AS
   3:  
   4: DECLARE @ViewName nvarchar(max)
   5: DECLARE @SQL nvarchar(max) 
   6:  
   7: DECLARE extensionViews CURSOR FOR
   8:     -- Get all views within the database
   9:     SELECT [name] As ViewName
  10:     FROM sys.views
  11:  
  12: OPEN extensionViews
  13:  
  14: FETCH NEXT FROM extensionViews
  15: INTO @ViewName
  16:  
  17: -- Check @@FETCH_STATUS to see if there are any more rows to fetch.
  18: WHILE @@FETCH_STATUS = 0
  19: BEGIN
  20:  
  21: -- Build the dynamic SQL for updating the view on the fetched row
  22: SET @SQL =
  23: 'IF EXISTS (SELECT * FROM sysobjects WHERE type = ''V'' AND name = ''' + @ViewName +''')
  24:     BEGIN
  25:         exec sp_refreshview N''dbo.'+ @ViewName + '''END'
  26:  
  27: exec(@SQL)
  28:      
  29:    -- This is executed as long as the previous fetch succeeds.
  30:    FETCH NEXT FROM extensionViews
  31:    INTO @ViewName
  32: END
  33:  
  34: CLOSE extensionViews
  35: DEALLOCATE extensionViews 
  36:  
  37: GO

Hope you find a use for this!

Greg Olsen
Yellow Duck Guy

Advertisements
Categories: SQL Server
  1. Jim P
    July 3, 2012 at 4:14 am

    This is good so long as the database views are all valid. If any of the underlying tables have been altered or dropped making the view invalid then the sql will stop at the first error. A workaround for SQL 2000 would be nice, I know 2005 + can use TRY and CATCH the errors but this syntax doesn’t exist in SQL 2000. Any ideas?

  2. MCW
    October 13, 2012 at 12:05 am

    thanks. sp_refreshview – thats what I was looking for. how many times have “support” got back to me with some nebulous error message, that when I trace is basically a dependant view. I think if you want to get round SQL2000 how about you hard code the calls to rebuild your views and log any fails. Better than scratching you head.

  3. Shenpen
    October 30, 2012 at 1:21 am

    Thank you, but why do you have these line numbers here? Cannot copy-paste without editing every single line!

  4. Ricardo Carvalho
    November 9, 2012 at 11:25 am

    thanksss!!!

    • November 9, 2012 at 8:52 pm

      @Ricardo Carvalho – no worries. Thanks for reading the post.

  5. Steve Stocker
    November 30, 2012 at 6:37 am

    Thanks for the inspiration

    ALTER PROCEDURE dbo.RefreshAllViews
    AS
    DECLARE @ViewName NVARCHAR(1024)
    DECLARE @Result NVARCHAR(MAX)
    DECLARE @Results TABLE (ViewName NVARCHAR(1024), Result NVARCHAR(MAX))
    DECLARE myViews CURSOR FOR SELECT [name] AS ViewName FROM sys.views V

    OPEN myViews FETCH NEXT FROM myViews INTO @ViewName

    WHILE @@FETCH_STATUS = 0
    BEGIN
    SET @Result = ‘Successfully refreshed’

    BEGIN TRY
    EXEC sp_refreshview @ViewName
    END TRY BEGIN CATCH
    SET @Result = error_message()
    ROLLBACK TRANSACTION /*avoids trancount error*/
    END CATCH

    INSERT INTO @Results VALUES(@ViewName, @Result)
    FETCH NEXT FROM myViews INTO @ViewName
    END

    SELECT * FROM @Results

    CLOSE myViews
    DEALLOCATE myViews

    GO

  6. Brian
    April 3, 2014 at 4:50 am

    Steve,
    I LOVE your refresh view procedure! As a quick note I added ‘[‘ + [name] + ‘]’ to the
    DECLARE myViews CURSOR FOR SELECT DISTINCT ‘[‘ + [name] + ‘]’ AS ViewName FROM sys.views V in order to deal with views that have a poor naming convention.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: