How do I create a stored procedure that calls sp_refreshview for each view in the database?

Question

Today I run this

select 'exec sp_refreshview N''['+table_schema+'].['+table_name+']'''
from information_schema.tables
where table_type = 'view'

This generates a lot of: exec sp_refreshview N'[SCHEMA].[TABLE]'. I then copy the result to the query editor window and run all those execs.

How do I do this all at once? I would like to have a stored procedure called something like dev.RefreshAllViews which I can execute to do this...

Answer 1

Check your system procedures. Particularly...

SP_ExecuteSQL

It takes a strind and executes it.

http://msdn.microsoft.com/de-de/library/ms188001.aspx

The rest is writing a SP that opens a cursor on the query above, and generates the proper strings and executes them ;)

Answer 2

DECLARE @RefreshScript varchar(max)
set @RefreshScript = ''


select @RefreshScript= @RefreshScript + 'exec sp_refreshview N''['+table_schema+'].['+table_name+']''
'
from information_schema.tables
where table_type = 'view'



exec (@RefreshScript)

If there is ever any danger of your views having the [] characters in their names you might want to look at the QUOTENAME function.

Or Also with a cursor

DECLARE @viewName AS VARCHAR(255)

    DECLARE listOfViews CURSOR
        FOR SELECT  '[' + SCHEMA_NAME(uid) + '].[' + name + ']'
            FROM    sysobjects
            WHERE   xtype = 'V'


    OPEN listOfViews

    FETCH NEXT FROM listOfViews INTO @viewName

    WHILE ( @@FETCH_STATUS <> -1 )
        BEGIN


            FETCH NEXT FROM listOfViews INTO @viewName

            BEGIN TRY
                EXEC sp_refreshview @viewName
                PRINT @viewName + ' refreshed OK'
            END TRY
            BEGIN CATCH
                PRINT @viewName + ' refresh failed'
            END CATCH
        END

    CLOSE listOfViews

    DEALLOCATE listOfViews