如何在 SQL Server 2008 中重建视图 [英] How to rebuild view in SQL Server 2008

问题描述

我的数据库中有一个视图，有人用一个表中的 * 定义了这个视图.我刚刚在该表中添加了一个新列，并且我希望视图反映新列.除了重新执行视图创建脚本之外，还有其他方法可以重建视图吗?我正在寻找类似于 sp_recompile 如何重新编译存储过程(或者更准确地将其标记为下次调用时编译)的方法.

更新:从长远来看，我尝试在视图上调用 sp_recompile，虽然调用有效，但它没有重建视图.

更新 2: 我希望能够从脚本中执行此操作.因此，将列添加到表的脚本也可以更新视图.所以就像我说的，类似于 sp_recompile.

解决方案

我相信您正在寻找的是

sp_refreshview [ @viewname = ] 'viewname'

<块引用>

更新指定的元数据非模式绑定视图.执着的视图的元数据可以变成由于更改而过时视图所基于的底层对象取决于.

见微软文档

There is a view in my DB that someone defined with a * from one table. I just added a new column to that table and I want the view to reflect the new column. Besides re-executing the view creation script, is there another way to rebuild the view? I am looking for something similar to how sp_recompile will recompile a stored procedure (or more accurately flag it to be compiled next time it is called).

Update: On a long shot I tried calling sp_recompile on the view and while the call worked, it didn't rebuild the view.

Update 2: I would like to be able to do this from a script. So the script that adds the columns to the table could also update the view. So like I said, something similar to sp_recompile.

解决方案

I believe what you're looking for is

sp_refreshview [ @viewname = ] 'viewname'

Updates the metadata for the specified non-schema-bound view. Persistent metadata for a view can become outdated because of changes to the underlying objects upon which the view depends.

See Microsoft Docs

这篇关于如何在 SQL Server 2008 中重建视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！