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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
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 |
Create underlying tables
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
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
|
1 2 3 4 5 6 7 |
SELECT * FROM vw_Sales; GO SELECT * FROM vw_Customers; GO |
Recreate all the views that depend on table Customers
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
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
|
1 2 3 4 5 6 7 |
SELECT * FROM vw_Sales; GO SELECT * FROM vw_Customers; GO |
Delete database
|
1 2 3 4 5 6 7 8 9 |
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 |