Solved

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

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

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 47

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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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 47

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

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.
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.
Viewers will learn how the fundamental information of how to create a table.

770 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