Solved

VB.net SQL Views don't incorporate new columns added to a table

Posted on 2016-10-04
6
59 Views
Last Modified: 2016-10-04
Hi
I have a number of Views in my SQL 2014 database that pull data from a number of tables. If I add new columns to one of the tables then run my application that reads the database schema to show all columns from the table doesn't update. Is this because the schema in the database hasn't been updated?
Is there a way to refresh all views so that they bring in new columns?
0
Comment
Question by:murbro
  • 3
  • 2
6 Comments
 
LVL 49

Accepted Solution

by:
Vitor Montalvão earned 500 total points
ID: 41827901
Views usually have static columns unless you create the view with SELECT * FROM ...
With that said, if you add a new column to a table that's part of a view then you need to update the view to have the new column returned (this is not a refresh, since there's such thing for views but an ALTER VIEW operation).
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41827911
In this case use below to get the newly added columns..

EXECUTE sp_refreshview N'ViewName';
0
 
LVL 49

Expert Comment

by:Vitor Montalvão
ID: 41827917
sp_refreshview does NOT add new columns. As it says in the MSDN article it only refreshes the metadata for the view (column data type change or relationships changes for example).
0
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41827924
I mean if the user added new columns in the table and if you have "SELECT *" in the view then one has to
use below to get the new column from the view.


EXECUTE sp_refreshview N'ViewName';

It is one of the famous limitation of the views
0
 
LVL 49

Expert Comment

by:Vitor Montalvão
ID: 41827929
It is one of the famous limitation of the views
Famous? What are the others limitations?
0
 

Author Closing Comment

by:murbro
ID: 41827952
Thanks very much
0

Featured Post

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I have a large data set and a SSIS package. How can I load this file in multi threading?
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question