sp_refreshview

Did you know that when you create a view and then later change the table the view is not updated?
Don't believe me? Run the following block of code

CREATE TABLE TestTable (id INT,SomeCol VARCHAR(666))
GO

INSERT TestTable VALUES(1,'ABC')
GO

SELECT * FROM TestTable
GO

CREATE VIEW TestView
AS
SELECT * FROM TestTable
GO

SELECT * FROM TestView
GO


ALTER TABLE TestTable
ADD Col2 DATETIME DEFAULT CURRENT_TIMESTAMP
GO

INSERT TestTable(id,SomeCol) VALUES(2,'XYZ')
GO

SELECT * FROM TestTable
GO

SELECT * FROM TestView
GO

See what happened? The TestView does not include the Col2 column. So what can you do? There are at least two things that you can do. You can recreate the view with a create or alter statement or you can use sp_refreshview, run the code below to see how that works

sp_refreshview TestView
GO

--All good now
SELECT * FROM TestView
GO


--Clean up this mess--
DROP VIEW TestView
GO


DROP TABLE TestTable
GO


And yes I know 'real' SQL programmers never use SELECT * and 'real' SQL programmers name their defaults ;-)