sp_refreshview (Transact-SQL)sp_refreshview (Transact-SQL)

適用於: 是SQL Server 否Azure SQL Database 否Azure Synapse Analytics (SQL DW) 否平行處理資料倉儲 APPLIES TO: yesSQL Server noAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

更新指定非結構描述繫結檢視的中繼資料。Updates the metadata for the specified non-schema-bound view. 檢視所依賴的基礎物件變更之後,檢視的保存中繼資料也可能會過期。Persistent metadata for a view can become outdated because of changes to the underlying objects upon which the view depends.

主題連結圖示 Transact-SQL 語法慣例Topic link icon Transact-SQL Syntax Conventions


sp_refreshview [ @viewname = ] 'viewname'   


[ @viewname = ] 'viewname' 是檢視的名稱。[ @viewname = ] 'viewname' Is the name of the view. viewnamenvarchar,沒有預設值。viewname is nvarchar, with no default. viewname可以是多部分識別碼,但只能參考目前資料庫中的檢視。viewname can be a multipart identifier, but can only refer to views in the current database.

傳回碼值Return Code Values

0 (成功) 或非零數字 (失敗)0 (success) or a nonzero number (failure)


如果未以 schemabinding 來建立檢視sp_refreshview檢視會影響檢視定義的物件進行變更時,應該執行。If a view is not created with schemabinding, sp_refreshview should be run when changes are made to the objects underlying the view that affect the definition of the view. 否則,在查詢檢視時,可能會產生非預期的結果。Otherwise, the view might produce unexpected results when it is queried.


必須對檢視具備 ALTER 權限,以及對檢視資料行所參考的 Common Language Runtime (CLR) 使用者自訂類型和 XML 結構描述集合,具備 REFERENCES 權限。Requires ALTER permission on the view and REFERENCES permission on common language runtime (CLR) user-defined types and XML schema collections that are referenced by the view columns.


A.A. 更新檢視的中繼資料Updating the metadata of a view

下列範例會重新整理 Sales.vIndividualCustomer 檢視的中繼資料。The following example refreshes the metadata for the view Sales.vIndividualCustomer.

USE AdventureWorks2012;  
EXECUTE sp_refreshview N'Sales.vIndividualCustomer';  

B.B. 建立可更新所有對於已變更物件具有相依性之檢視的指令碼Creating a script that updates all views that have dependencies on a changed object

假設資料表 Person.Person 是以會影響在任何檢視上建立的定義之方式來變更。Assume that the table Person.Person was changed in a way that would affect the definition of any views that are created on it. 下列範例建立了一個指令碼,針對所有對資料表 Person.Person 有相依性的檢視來重新整理中繼資料。The following example creates a script that refreshes the metadata for all views that have a dependency on table Person.Person.

USE AdventureWorks2012;  
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('Person.Person');  

另請參閱See Also

Database Engine 預存程序(Transact SQL) Database Engine Stored Procedures (Transact-SQL)
系統預存程序 (Transact-SQL) System Stored Procedures (Transact-SQL)
sys.sql_expression_dependencies (Transact-SQL) sys.sql_expression_dependencies (Transact-SQL)
sp_refreshsqlmodule (Transact-SQL)sp_refreshsqlmodule (Transact-SQL)