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 😉

*** If you have a SQL related question try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum