Solved

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

Posted on 2016-10-04
6
53 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 48

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 48

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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
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 48

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

840 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