Very often we make changes to the design of an underlying objects, used by views. After we do this, we need to refresh the views to apply the changes.

In this example we will:
1. Create View
2. Change the design of an underlying object
3. Refresh all the views that depend on the object, which design is changed

Create database

USE [master];
GO

IF (DB_ID('PL') IS NOT NULL)
	BEGIN
		ALTER DATABASE PL SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
		DROP DATABASE PL;
	END
GO

CREATE DATABASE PL;
GO

USE PL;
GO

IF (OBJECT_ID('Sales', 'U') IS NOT NULL)
	BEGIN DROP TABLE Sales; END
GO

IF (OBJECT_ID('Customers', 'U') IS NOT NULL)
	BEGIN DROP TABLE Customers; END
GO

CREATE TABLE Customers
	(
		ID INT IDENTITY(1, 1) PRIMARY KEY
		, FirstName NVARCHAR(32)
		, LastName NVARCHAR(64)
	);
GO

INSERT Customers	
SELECT 'Peter', 'Lalovsky'
UNION SELECT 'John', 'Smith';
GO

SELECT *
FROM Customers;
GO

CREATE TABLE Sales
	(
		ID INT IDENTITY(1, 1) PRIMARY KEY
		, CustomerID INT FOREIGN KEY REFERENCES Customers (ID)
		, ItemID INT
		, Quantity INT
		, Price MONEY
		, DateOrdered DATETIME
	);
GO

INSERT Sales (CustomerID, ItemID, Quantity, Price, DateOrdered)
SELECT 1, 1, 3, 0.25, '2014-08-12'
UNION SELECT 2, 3, 12, 5.23, '2014-12-15'
UNION SELECT 1, 3, 7, 5.23, '2015-05-03'
UNION SELECT 1, 5, 6, 1.28, '2013-04-17';
GO

SELECT *
FROM Sales;
GO

Create views

IF (OBJECT_ID('vw_Sales', 'V') IS NOT NULL)
	BEGIN DROP VIEW vw_Sales; END
GO

CREATE VIEW vw_Sales
AS
	SELECT
		S.DateOrdered
		, S.ItemID
		, '<Sales | Customers>' AS [|]
		, C.*
	FROM
		PL.dbo.Customers AS C
		JOIN PL.dbo.Sales AS S
			ON C.ID = S.CustomerID;
GO

SELECT *
FROM vw_Sales;
GO

IF (OBJECT_ID('vw_Customers', 'V') IS NOT NULL)
	BEGIN DROP VIEW vw_Sales; END
GO

CREATE VIEW vw_Customers
AS
	SELECT
		C.FirstName
		, C.LastName
		, '<Customers | Sales>' AS [|]
		, SUM(S.Quantity * S.Price) AS Sales
	FROM
		PL.dbo.Customers AS C
		JOIN Sales AS S
			ON C.ID = S.CustomerID
	GROUP BY
		C.FirstName
		, C.LastName
	GO

SELECT *
FROM vw_Customers;
GO

ALTER Customers

ALTER TABLE Customers
ADD Email VARCHAR(128);
GO

UPDATE Customers
SET Email = 'peter@lalovsky.com'
WHERE
	FirstName = 'Peter'
	AND LastName = 'Lalovsky';
GO

UPDATE Customers
SET Email = 'john.smith@customer1.com'
WHERE
	FirstName = 'John'
	AND LastName = 'smith';
GO

SELECT *
FROM Customers;
GO

Test the views

SELECT *
FROM vw_Sales;
GO

SELECT *
FROM vw_Customers;
GO

Recreate all the views that depend on table Customers

DECLARE
	@ReferencingName SYSNAME
	, @SQL VARCHAR(MAX);

DECLARE ReferencingName_Cursor CURSOR LOCAL FAST_FORWARD FOR
	SELECT OBJECT_NAME([referencing_id]) AS ReferencingName
	FROM
		PL.sys.sql_expression_dependencies AS SED
		JOIN PL.sys.objects AS O
			on SED.referencing_id = O.[object_id]
	WHERE
		[referenced_id] = OBJECT_ID('Customers')
		AND O.[type] = 'V';

OPEN ReferencingName_Cursor;
FETCH NEXT FROM ReferencingName_Cursor INTO @ReferencingName;

WHILE @@FETCH_STATUS = 0
BEGIN
	SET @SQL = '
EXEC sp_refreshview ''' + @ReferencingName + ''';
	';

	EXEC (@SQL);

	FETCH NEXT FROM ReferencingName_Cursor INTO @ReferencingName;
END

CLOSE ReferencingName_Cursor;
DEALLOCATE ReferencingName_Cursor;
GO

Test the views

SELECT *
FROM vw_Sales;
GO

SELECT *
FROM vw_Customers;
GO

Delete database

USE [master];
GO

IF (DB_ID('PL') IS NOT NULL)
	BEGIN
		ALTER DATABASE PL SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
		DROP DATABASE PL;
	END
GO