Solved

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

Posted on 2016-10-04
6
40 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 46

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 24

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 46

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 24

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 46

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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

920 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now