Table 更新需要對應的跟新的 VIEW (sp_refreshview)

After creating a view, the meta data of the view will be stored and will not change even the table that relies is changed.

There are many ways to solve the problem. The first one is drop the view and create the view. Power Designer is good at that. This tool will generate the complete script to do that. However there is anther stored procedure to support this. it is sp_refreshview.

command example:
sp_refreshview v_name


In the mean time, we could run the following script to retrieve all the views that should be rerun sp_refreshview once the table is changed:


SELECT DISTINCT 'EXEC sp_refreshview ''' + name + ''''
FROM sys.objects so INNER JOIN sys.sql_dependencies sd
ON so.object_id = sd.object_id
WHERE type = 'V'
AND sd.referenced_major_id = object_id('TABLE_WHO_IS_CHANGED');