当删除视图的一部分字段时，SQL服务器不会发出警报

I just don´t understand why SQL Server doesn´t throw an error when you delete a column from a table, and that column is read by a View. To my understanding, SQL Server has that information, knows which columns/tables are read when you do a select query from a view, so i think it shoulnd´t let you delete a column referenced from a view, just like it doesn´t allow you to remove a column referenced by a FK... There has to be an explanation to this, please someone tell me! Thanks a lot.

´我只是不明白为什么SQL Server并´t抛出一个错误当你删除表中的一列,列是阅读一个视图。我理解,SQL服务器信息,知道哪些列/表读当你从一个视图选择查询,所以我认为这shoulnd´t让你从一个视图删除列引用,就像它并´t允许您删除一列引用的一颗……这事总得有个解释，请告诉我吧!非常感谢。

2 个解决方案

#1

Because you have to ask SQL Server to do this tracking. The option is called SCHEMABINDING:

因为您必须要求SQL Server执行此跟踪。该选项称为SCHEMABINDING:

Binds the view to the schema of the underlying table or tables. When SCHEMABINDING is specified, the base table or tables cannot be modified in a way that would affect the view definition

将视图绑定到底层表或表的模式。当指定SCHEMABINDING时，不能以影响视图定义的方式修改基表或表。

The same option is also available on user defined functions, but unfortunately not on stored procedures.

用户定义函数也可以使用相同的选项，但不幸的是存储过程中没有。

It's not set by default - it's possible to construct a view that resolves its tables to different base tables for different users (assuming the users have different default schemas)

它不是默认设置的——可以构造一个视图，将其表解析为不同用户的不同基表(假设用户具有不同的默认模式)

#2

You need to refresh the views. Here is a script that refresh all the view on a database:

您需要刷新视图。下面的脚本可以刷新数据库上的所有视图:

DECLARE @ViewName AS VARCHAR(255)
DECLARE @tblErrorViews TABLE(ViewName VARCHAR(255))
DECLARE listOfView CURSOR FOR
    SELECT [Name] FROM sysobjects 
    WHERE xtype = 'V'
    AND uid = SCHEMA_ID('dbo')

OPEN listOfView

FETCH NEXT FROM listOfView into @ViewName

WHILE (@@FETCH_STATUS <> -1)
    BEGIN
        FETCH NEXT FROM listOfView INTO @ViewName
            print @ViewName
            EXEC sp_refreshview @ViewName
    END

CLOSE listOfView
DEALLOCATE listOfView

The you will know which view that needs your attention

你会知道哪个观点需要你的注意

#1