当修改一个表结构时(增加和删除列),引用到该表的视图并不会自动更新,这样就有可能导致一些问题。
比如:
有一个表T1有两列C1,C2.
视图VW1为:CREATE VIEW VW1 AS SELECT * FROM T1
如果增加列C3后,视图VW1执行的结果还是只有两列C1,C2
有几种方法来手动更新这个视图
1)重新执行视图脚本:CREATE VIEW VW1 AS SELECT * FROM T1
2)调用系统存储过程“ sp_refreshview”,来更新视图:EXEC sp_refreshview [VW1]
(试了 sp_recompile [VW1] 不起作用,不知它重新编译时干了什么)
这是知道该表被哪些视图引用的情况,如果不知道该表被哪些视图引用,可以用以下脚本:
SELECT DISTINCT 'EXEC sp_refreshview ''' + name + ''''
FROM sys.objects so INNER JOIN sys.sql_dependencies sd
ON so.object_id = sd.object_id
WHERE so.type = 'V'
AND sd.referenced_major_id = object_id('T1')
同理,也可找出引用它的存储过程来手动做一些修改:
SELECT DISTINCT name
FROM sys.objects so INNER JOIN sys.sql_dependencies sd
ON so.object_id = sd.object_id
WHERE so.type = 'P'
AND sd.referenced_major_id = object_id('T1')
或者干脆遍历更新一遍所有视图: http://www.ssw.com.au/SSW/KB/KB.aspx?KBID=Q1000592
DECLARE @ObjectName varchar (255)
DECLARE tnames_cursor CURSOR FOR SELECT name FROM sysobjects
WHERE type = 'V' AND uid = 1 Order By Name
OPEN tnames_cursor
FETCH NEXT FROM tnames_cursor INTO @Objectname
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
SELECT @ObjectName_header = 'Refreshing ' + @ObjectName
PRINT @ObjectName_header
EXEC('sp_refreshview ' + @ObjectName)
END
FETCH NEXT FROM tnames_cursor INTO @ObjectName
END
比如:
有一个表T1有两列C1,C2.
视图VW1为:CREATE VIEW VW1 AS SELECT * FROM T1
如果增加列C3后,视图VW1执行的结果还是只有两列C1,C2
有几种方法来手动更新这个视图
1)重新执行视图脚本:CREATE VIEW VW1 AS SELECT * FROM T1
2)调用系统存储过程“ sp_refreshview”,来更新视图:EXEC sp_refreshview [VW1]
(试了 sp_recompile [VW1] 不起作用,不知它重新编译时干了什么)
这是知道该表被哪些视图引用的情况,如果不知道该表被哪些视图引用,可以用以下脚本:
SELECT DISTINCT 'EXEC sp_refreshview ''' + name + ''''
FROM sys.objects so INNER JOIN sys.sql_dependencies sd
ON so.object_id = sd.object_id
WHERE so.type = 'V'
AND sd.referenced_major_id = object_id('T1')
同理,也可找出引用它的存储过程来手动做一些修改:
SELECT DISTINCT name
FROM sys.objects so INNER JOIN sys.sql_dependencies sd
ON so.object_id = sd.object_id
WHERE so.type = 'P'
AND sd.referenced_major_id = object_id('T1')
或者干脆遍历更新一遍所有视图: http://www.ssw.com.au/SSW/KB/KB.aspx?KBID=Q1000592
DECLARE @ObjectName varchar (255)
DECLARE tnames_cursor CURSOR FOR SELECT name FROM sysobjects
WHERE type = 'V' AND uid = 1 Order By Name
OPEN tnames_cursor
FETCH NEXT FROM tnames_cursor INTO @Objectname
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
SELECT @ObjectName_header = 'Refreshing ' + @ObjectName
PRINT @ObjectName_header
EXEC('sp_refreshview ' + @ObjectName)
END
FETCH NEXT FROM tnames_cursor INTO @ObjectName
END