刷新视图

问题：

---

 

当我们创建这样一个视图（myView）：

create view myView As select * from myTable

 

注：myTable的结构：myTable(id int)

后来，我们改变了myTable的结构，增加一个字段（newCol）：

alter table myTable add newCol nvarchar(50)

当我们执行查询视图操作，发现返回的是未修改myTable表结构前的字段ID，即少了newCol字段：

 

 

 

            图1.

 

分析：

---

 

 开始的时候，我们创建的视图myView，

‘create view myView As select * from myTable’

sql server 会把中myTable的所有字段（即修改结构前的ID字段）存储在系统表中，形成一个持久元数据。针对视图对象，我们可以通过系统视图查询sys.objects & sys.columns來查詢：

select b.*
    from sys.objects a
        inner join sys.columns b on b.object_id=a.object_id
    where a.name='myView'

 

                                                                                              图2.

在图2.我们可以看到视图返回的字段，包含字段名，数据类型，长度，精度，是否为null等信息，如果我们查回myTable中的ID字段信息，会发现与视图的描述一致：

select b.*
    from sys.objects a
        inner join sys.columns b on b.object_id=a.object_id
    where a.name='myTable'

 

 

                                                                                            图3

如果，我们修改了myTable表结构，会发现sys.columns没有同时更新，查询的结果与图2一致。也是说视图对象myView的持久元数据没有更新到，我们查询视图的结构还是修改前的。

 

解决：

---

 

 

针对问题，sql server 专门一个系统存储过程来更新视图对象的元数据sp_refreshview   ：

sp_refreshview [ @viewname = ] 'viewname' 

 或使用系统存储过程：sp_refreshsqlmodule   

sys.sp_refreshsqlmodule [ @name = ] 'module_name' 
    [ , [ @namespace = ] ' <class> ' ]

 

扩展：

---

 这里我写了一个通用的存储过程来更新类似的问题，实现功能是：

更新当前数据库中指定的非绑定到架构的存储过程、用户定义函数、视图、DML 触发器的元数据

use master
go
if object_id('sp_RefreshSqlModule_All') Is not null
    Drop proc sp_RefreshSqlModule_All
go
create proc sp_RefreshSqlModule_All
As
/*更新当前数据库中指定的非绑定到架构的存储过程、用户定义函数、视图、DML 触发器的元数据*/

declare @sql nvarchar(max)

select @sql=isnull(@sql+Char(13)+char(10),'')+'exec sys.sp_refreshsqlmodule '''+quotename(b.name)+'.'+quotename(a.name)+''';'
    from sys.objects a 
        inner join sys.schemas b on b.schema_id=a.schema_id
    where a.type in ('P','V','TR','FN','IF','TF')
        and a.is_ms_shipped=0
        and a.name <>'sp_RefreshSqlModule_All'
        and not exists(select 1 
                            from sys.extended_properties x
                            where x.major_id=a.object_id
                                and x.minor_id=0
                                and x.class=1
                                and x.name='microsoft_database_tools_support'
                        )

exec(@sql)
print '已更新存储过程，用户定义函数，视图，DML触发器的元数据！'    
go

exec sp_ms_marksystemobject 'sp_RefreshSqlModule_All'
go

 

注：‘exec sp_ms_marksystemobject 'sp_RefreshSqlModule_All'’ 注册为系统对象，这样在每一个数据库下都可以灵活调用。

调用方法：

该存储过程已在下面環境測試通過：

SQL Server 2005/2005/2008R2/2012

注：不支持SQL Server 2000