Why my view does not show this column?

Arkadiusz Fronc SQL Server 17 December 2017

view When using SQL Server you may have an impression that not everything works exactly how you would expect. One of those things may be a view that does not contain a column that you added to the source table. In this article I will explain you why it works like that and how to refresh columns in the view.

Table and view

This issue is easily reproducible by creating a table in SQL Server.

create table ARTICLES (
       Art_Author varchar(255),
       Art_Title varchar(255)
);
GO

I create a view using that table.

create view VIEW_ARTICLES
as
       select *
       from ARTICLES;
GO

It is a very simple example as this view uses only one table and there are no joins or even data filtering. It is done that way for simplicity purposes. You can check it on more complex cases if you want.

I add a row to the table. It is not necessary but a result set will look better.

insert into ARTICLES (Art_Author, Art_Title)
values ('DBA presents', 'Why this view does not show this column?');

The view can be queried.

select *
from VIEW_ARTICLES

select1

Two columns are returned.

Change in table

At this point I have a table and a view that are in sync. It will not be true when I modify the table e.g. add a column.

alter table ARTICLES
       add Art_IsPublished bit;

Now, the table contains three columns but the view still returns two.

select *
from VIEW_ARTICLES

select1

What did happen?

Explanation is simple. SQL Server stores meta data about the view separately instead of parsing the query used for the view creation each time the view is accessed.

To prove that, you can check what SQL Server knows about the columns of VIEW_ARTICLES.

infoSchema1

It still thinks that the view returns two columns even though the table has three columns.

How to refresh view's meta data

Meta data of the view can be refreshed by recreating a view or altering it. It is not always the best option because by recreating the view permissions are reset. Altering the view requires having a full CREATE VIEW statement and the query used underneath the view.

Much better way of doing it is sp_refreshview procedure.

exec sp_refreshview 'VIEW_ARTICLES'

It forces SQL Server to parse the query that reads data for the view, check columns, data types and update meta data. After executing sp_refreshview, the database has updated information.

infoSchema2

All three columns are now accessible.

select *
from VIEW_ARTICLES

select2