sp_refreshview (update metadata/definition of a view)

sp_refreshview System stored procedure


USE TEMPDB

BEGIN TRANSACTION

--*********CREATE table temp_Employee without column Location **************
CREATE Table temp_Employee
(EmpId INT,
Name VARCHAR(100),
Department VARCHAR(100))

--*********Insert data into table temp_Employee **************
INSERT INTO temp_Employee (EmpId ,Name,Department )
VALUES (1, 'Allen','IT')
GO

--*********CREATE VIEW on Table temp_Employee **************
CREATE VIEW vw_temp_Employee
AS
SELECT * FROM temp_Employee
GO


--*********SELECT Data from Table and View both query returns same number of columns **************
SELECT TOP 1 * FROM temp_Employee
SELECT TOP 1 * FROM vw_temp_Employee


--*********ALTER TABLE temp_Employee Add New column Location **************
ALTER TABLE temp_employee
ADD Location VARCHAR(100)


--*********SELECT Data from Table and View both query do not returns same number of columns(view is not returning Location Column) **************
SELECT TOP 1 * FROM temp_Employee
SELECT TOP 1 * FROM vw_temp_Employee


-- Now Execute sp_refreshview to update the view definition in System defined module object
EXEC sp_refreshview vw_temp_Employee


--*********SELECT Data from Table and View both query returns same number of columns **************
SELECT TOP 1 * FROM temp_Employee
SELECT TOP 1 * FROM vw_temp_Employee

ROLLBACK TRANSACTION