sp_refreshview or how to make sure that the view will have the underlying table changes
Got a question about this on our Microsoft SQL Server Programming Forum so you know it is time for a quick blog post.
Did you know that when you create a view and then later change the table the view is not updated?
let me show you what I mean.
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
Now we will change that table by adding another column
ALTER TABLE TestTable ADD Col2 DATETIME DEFAULT CURRENT_TIMESTAMP GO INSERT TestTable(id,SomeCol) VALUES(2,'XYZ') GO
Now run the selects again
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 😉