CODEWITHSUNDEEP
sql-server
Colin
Colin

Reputation: 22595

How do I get dependent views to update automatically in SQL Server?

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

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?

Upvotes: 4

Views: 2425

Answers (2)

Aaron Bertrand
Aaron Bertrand

Reputation: 280413

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;

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

Upvotes: 6

Mitch Wheat
Mitch Wheat

Reputation: 300669

sp_refreshview:

sp_refreshview [ @viewname = ] 'viewname'

SQL Server – refresh all Views

Upvotes: 2

Related Questions