刷新视图
问题:
当我们创建这样一个视图(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
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· go语言实现终端里的倒计时
· 如何编写易于单元测试的代码
· 10年+ .NET Coder 心语,封装的思维:从隐藏、稳定开始理解其本质意义
· .NET Core 中如何实现缓存的预热?
· 从 HTTP 原因短语缺失研究 HTTP/2 和 HTTP/3 的设计差异
· 周边上新:园子的第一款马克杯温暖上架
· 分享 3 个 .NET 开源的文件压缩处理库,助力快速实现文件压缩解压功能!
· Ollama——大语言模型本地部署的极速利器
· DeepSeek如何颠覆传统软件测试?测试工程师会被淘汰吗?
· 使用C#创建一个MCP客户端