my-view-isnt-reflecting-changes-ive-made-to-the-underlying-tables
FROM http://sqlstudies.com/2013/01/20/my-view-isnt-reflecting-changes-ive-made-to-the-underlying-tables/
Problem: You’ve added columns to the base table of one of your views, but the view isn’t reflecting the change.
Over the years I’ve seen lot’s of views created similar to this one.
|
1
2
|
CREATE VIEW vw_TableView AS SELECT * FROM TableName |
Generally the argument is that if I put “SELECT *” rather than an explicit field list, then when my table changes so will my view. Unfortunately it doesn’t work that way.
Let’s try an example.
Create a test table and populate it with some data.
|
1
2
3
4
5
6
7
8
9
10
|
CREATE TABLE TableName (Column1 varchar(10))GOINSERT INTO TableName VALUES ('abcdefg')INSERT INTO TableName VALUES ('hij')INSERT INTO TableName VALUES ('klmnop')INSERT INTO TableName VALUES ('qrstuvwxy')INSERT INTO TableName VALUES ('zabcde')INSERT INTO TableName VALUES ('123456')GO |
Create a test view.
|
1
2
3
|
CREATE VIEW vw_TableView AS SELECT * FROM TableNameGO |
Test the view to make sure we are getting the data we expect.
|
1
2
|
SELECT * FROM vw_TableViewGO |
So far so good. The output is exactly what we expected. Now let’s add a column to the table and populate it.
|
1
2
3
4
|
ALTER TABLE TableName ADD Column2 INTGOUPDATE TableName SET Column2 = 3GO |
And try out the view again.
|
1
2
|
SELECT * FROM vw_TableViewGO |
| Column1 |
| abcdefg |
| hij |
| klmnop |
| qrstuvwxy |
| zabcde |
| 123456 |
Now wait just a minute. The output I’m getting looks exactly like it did before I added Column2. All I’m seeing is Column1. Now the first thing I do when debugging something like this is make sure the view should in fact be pulling the new column. So:
|
1
|
EXEC sp_helptext vw_TableView |
|
1
2
3
4
5
|
Text---------------------------------------------------------------CREATE VIEW vw_TableView AS SELECT * FROM TableName |
Ok, so the code still looks correct. So why aren’t we pulling all of the columns even though we are using a *? From what I understand the metadata for the view is not automatically updated when the tables are modified.
The fix is to either drop and re-create or alter the view or to use the sp_refreshview stored procedure. Sp_refreshview has the combined benefit of being the simplest method and not messing up any explicit permissions on the view caused by dropping it.
|
1
2
|
EXEC sp_RefreshView vw_TableViewGO |
And test the view again.
|
1
2
|
SELECT * FROM vw_TableViewGO |
| Column1 | Column2 |
| abcdefg | 3 |
| hij | 3 |
| klmnop | 3 |
| qrstuvwxy | 3 |
| zabcde | 3 |
| 123456 | 3 |
And now we have the correct number of columns for our view.
Next let’s try going the other way. We remove a column from the table.
|
1
2
|
ALTER TABLE TableName DROP Column2GO |
And we try querying the view again. (I’m hoping no one expects it to work correctly.)
|
1
2
|
SELECT * FROM vw_TableViewGO |
This time we get an error.
|
1
2
|
Msg 4502, Level 16, State 1, Line 1View or function 'vw_TableView' has more column names specified than columns defined. |
If we again run sp_refreshview then the view will once again show the expected data.
|
1
2
3
4
|
EXEC sp_RefreshView vw_TableViewGOSELECT * FROM vw_TableViewGO |
| Column1 |
| abcdefg |
| hij |
| klmnop |
| qrstuvwxy |
| zabcde |
| 123456 |
And last but not least some cleanup code.
|
1
2
3
|
DROP VIEW vw_TableViewDROP TABLE TableNameGO |
最新文章
- Counting-Sort
- leetcode 9
- ajax练习习题二三级联动
- [Java] SSH框架笔记_S2SH整合步骤
- BZOJ 2241: [SDOI2011]打地鼠 暴力
- textarea文本域宽度和高度（width、height）自己主动适应变化处理
- （转帖）oracle sql 语句优化
- IIS网站 由http协议改变为https协议
- spring activemq 整合
- CountDownLatch & CyclicBarrier
- Linux中的do{...} while(0)
- PAT甲题题解-1095. Cars on Campus(30)-（map+树状数组，或者模拟）
- 高性能JSON框架之FastJson的简单使用
- Linux rpm命令详解
- [PHP] ubuntu 16.04系统下解决MySQL 5.7版本的root用户重置密码问题
- Java：泛型擦除
- Oracle日志组添加冗余文件和日志组
- java中ThreadLocalRandom类和Random类的使用
- Delphi 询问框 汉化
- Rancher探秘一：初识Rancher