Refreshing dependent views after a DDL change

When you are in charge of databases with tons of objects, making a small change over an object can be difficult to replicate if you have a lot of objects that depend on it.

For this post, we will focus on how to update column changes on dependent views.

For this type of task, there are 2 scenarios:

• You explicitly defined column names on the dependent views, or defined WITH SCHEMABINDING: For this case, you need to perform a find/replace task, this can be difficult to achieve since there are no native tools to do it (as far as I know). You can use third-party free tools like RedGate SQL Search or ApexSQL SQL Search, both having its pro and cons, so take a look at both tools and find out what it suits best your needs.

• You implicitly defined column names on dependant views: The famous SELECT *, even when this is not a best practice, it is easy to replicate changes for this case, we will show you how to do it.

Setting up our example

We will use WideWorldImporters test database for this example, we create two simple views (one depending on the other), as follows:

USE WideWorldImporters;
GO
CREATE VIEW dbo.[vi_invoices_received_by]
AS
SELECT 
    ConfirmedReceivedBy, 
COUNT(InvoiceID) as [Num],
    CustomerID
FROM Sales.Invoices
GROUP BY ConfirmedReceivedBy, CustomerID;
GO

CREATE VIEW dbo.[vi_additional_fields_received_by]
AS 
SELECT RB.*,
C.CustomerName 
FROM dbo.vi_invoices_received_by RB
INNER JOIN sales.Customers C
ON C.CustomerID = RB.CustomerID;
GO

We proceed to execute the second view, you can see that the column labels of the first view are not so descriptive, and can be improved:

So we proceed to execute an alter view over the first view:

ALTER VIEW dbo.[vi_invoices_received_by]
AS
SELECT 
    ConfirmedReceivedBy as [Received by], 
COUNT(InvoiceID) as [# of Invoices],
    CustomerID
FROM Sales.Invoices
GROUP BY ConfirmedReceivedBy, CustomerID;
GO

So we should now be able to see the updated column names on the second view, right?

but if you execute the view again, you will obtain the same results as before:

What happened?

Refreshing your dependent views

SQL Server provides the procedure sp_refreshview to update metadata of views, so you can use it to update the schema without having to recreate the objects.

For our case, you just have to execute the following code against the second view:

EXEC sp_refreshview 'dbo.[vi_additional_fields_received_by]'; 
GO

If we execute the second view again, we can see that the labels have been updated now:

And that is all, you just have to execute the procedure for each dependent view.

What if I don't know the dependent objects?

There are several ways to determine what objects depend on the one you are modifying, I will show you two of them.

• Via T-SQL: Execute the sp_depends procedure, it will return two subsets: the first one, are objects on which our current object depends. The second one objects that depend on the current object. Usage is as follows:

• Via SSMS: Just right-click on the object and select View Dependencies