如何让依赖视图在 SQL Server 中自动更新? [英] How do I get dependent views to update automatically in SQL Server?

问题描述

我们公司的某个人决定使用 3 个视图来支持一个网页，并且它们必须包含相同的列子集.所以我有这样的定义:

Somebody at our firm decided to use 3 views to support a webpage, and they have to contain a subset of columns that are identical. So I have definitions like this:

CREATE VIEW EmailReceivedView
AS
SELECT     
    dbo.EmailReceived.ID
    , ...lots of columns
FROM  dbo.EmailReceived 
LEFT OUTER JOIN ...more tables

--Emails related to reviews
CREATE VIEW ReviewEmailReceivedView
AS
SELECT RV.ReviewID, V.*
FROM ReviewEmailReceived RV
INNER JOIN EmailReceivedView V ON EmailReceivedID = V.ID

--Emails related to grants
CREATE VIEW GrantEmailReceivedView
AS
SELECT GV.GrantID, V.*
FROM GrantEmailReceived GV 
INNER JOIN EmailReceivedView V ON GV.EmailReceivedID = V.ID

现在我在从属视图中做 V.* 的原因是，如果支持视图发生变化，我希望从属视图反映变化.但这不会发生在 SQL Server 中，除非我将脚本重新运行为 ALTER 脚本.为什么不?有没有办法确保对支持视图的更改自动反映在依赖项中?

Now the reason I did V.* in the dependent views is that in the event that the supporting view changes, I want the the dependent views to reflect the changes. But that doesn't happen in SQL Server unless I rerun the scripts as ALTER scripts. Why not? And is there a way to ensure that changes to a supporting view are automatically reflected in the dependencies?

推荐答案

还有 sp_refreshsqlmodule 我更喜欢它，因为你也可以将它用于不是视图的模块.不过，要构建一个刷新所有视图的脚本，您可以这样做:

There is also sp_refreshsqlmodule which I prefer only because you can also use it for modules that aren't views. To build a script that refreshes all views, though, you could do this:

DECLARE @sql NVARCHAR(MAX);
SET @sql = N'';

SELECT @sql = @sql + N'
  EXEC sys.sp_refreshsqlmodule 'N'' 
  + QUOTENAME(s.name) + N'.'
  + QUOTENAME(v.name) + N''';'
FROM sys.views AS v
INNER JOIN sys.schemas AS s
ON v.[schema_id] = s.[schema_id]
WHERE v.is_ms_shipped = 0
-- AND v.name LIKE '%EmailReceivedView%'
;

PRINT @sql;
-- EXEC sys.sp_executesql @sql;

但更重要的是，这正是为什么你不应该在视图中使用 SELECT *.

But more to the point, this is exactly why you shouldn't use SELECT * in a view.

这篇关于如何让依赖视图在 SQL Server 中自动更新?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！