刷新视图:视图是从物理表中按条件提取出来的‘表’,当原始基表结构发生变化时,视图并不与之同步更新,需要手动刷新
A:
use tempdb
go
if object_id('dbo.V1') is not null
drop view dbo.V1;
Go
if object_id('dbo.T1') is not null
drop table dbo.T1;
go
create table dbo.T1(col1 int ,col2 int );
insert into dbo.T1(col1,col2) values(1,2);
Go
create view dbo.V1
as select * from dbo.T1;
GO
select * from V1
B:
alter table dbo.T1 add col3 int;
select * from V1 该语句执行时,还是原始的基表结构
C:exec sp_refreshview 'dbo.V1' 当执行此操作后,再次运行select * from V1,结构与更新表一致.
为了在每次刷新表结构的同时都刷新视图,运行如下语句:
select N' exec sp_refreshview '
+quotename(view_name,'''')+';' as cmd
from (select quotename(table_schema) +N'.'+quotename(table_name) as view_name from information_schema.views ) as V
where objectproperty(object_id(view_name),'isShcemaBound')=0;
以上语句得到未刷新的视图列表