如何创建为数据库中的每个视图调用 sp_refreshview 的存储过程？

发布于 2024-08-26 01:47:34 字数 324 浏览 4 评论 0原文

今天我运行这个

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

这会生成很多：exec sp_refreshview N'[SCHEMA].[TABLE]'。然后，我将结果复制到查询编辑器窗口并运行所有这些执行程序。

我如何一次完成这一切？我想要一个名为 dev.RefreshAllViews 之类的存储过程，我可以执行它来执行此操作...

原文

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

梦中楼上月下 2024-09-02 01:47:34

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)

如果您的视图名称中存在 [] 字符的危险，您可能需要查看 QUOTENAME 函数。

或者也可以使用光标

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

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

回复收藏 0 原文

聚集的泪 2024-09-02 01:47:34

DECLARE @Sql VARCHAR(MAX) = ''

SELECT @Sql += 'EXEC sys.sp_refreshview @viewname = N''' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) + '''' + CHAR(10) 
FROM INFORMATION_SCHEMA.VIEWS

PRINT @Sql
EXEC(@Sql)

DECLARE @Sql VARCHAR(MAX) = ''

SELECT @Sql += 'EXEC sys.sp_refreshview @viewname = N''' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) + '''' + CHAR(10) 
FROM INFORMATION_SCHEMA.VIEWS

PRINT @Sql
EXEC(@Sql)

回复收藏 0 原文