Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 94
  • Last Modified:

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

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
Murray Brown
Asked:
Murray Brown
  • 3
  • 2
1 Solution
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
Pawan KumarDatabase ExpertCommented:
In this case use below to get the newly added columns..

EXECUTE sp_refreshview N'ViewName';
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
Pawan KumarDatabase ExpertCommented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
It is one of the famous limitation of the views
Famous? What are the others limitations?
0
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Thanks very much
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now