Solved

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

Posted on 2016-10-04
6
73 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:Murray Brown
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 51

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 29

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 51

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
[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

 
LVL 29

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 51

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:Murray Brown
ID: 41827952
Thanks very much
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

623 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