更改表字段后刷新视图

CREATE PROCEDURE RefreshAllView AS
DECLARE MyCursor CURSOR
FOR select Name from dbo.sysobjects where  OBJECTPROPERTY(id, N'IsView') = 1 and (not name in ('sysconstraints','syssegments'))

DECLARE @name varchar(40)
OPEN MyCursor

FETCH NEXT FROM MyCursor INTO @name
WHILE (@@fetch_status <> -1)
BEGIN
    IF (@@fetch_status <> -2)
    begin
     exec sp_refreshview @name
             end
    FETCH NEXT FROM MyCursor INTO @name
END

CLOSE MyCursor
DEALLOCATE MyCursor

 

 

 

SELECT DISTINCT 'EXEC sp_refreshview ''' + name + '''' 
FROM sys.objects AS so 
INNER JOIN sys.sql_expression_dependencies AS sed 
ON so.object_id = sed.referencing_id 
WHERE so.type = 'V' AND sed.referenced_id = OBJECT_ID('dbo.Enterprise');