Skip to content

Latest commit

 

History

History
85 lines (61 loc) · 3.05 KB

sp-refreshview-transact-sql.md

File metadata and controls

85 lines (61 loc) · 3.05 KB
title description author ms.author ms.reviewer ms.date ms.service ms.subservice ms.topic f1_keywords helpviewer_keywords dev_langs monikerRange
sp_refreshview updates the metadata for the specified non-schema-bound view.
sp_refreshview (Transact-SQL)
markingmyname
maghan
randolphwest
08/21/2024
sql
system-objects
reference
sp_refreshview
sp_refreshview_TSQL
sp_refreshview
TSQL
=azuresqldb-current || >=sql-server-2016 || >=sql-server-linux-2017 || =azuresqldb-mi-current

sp_refreshview (Transact-SQL)

[!INCLUDE sql-asdb-asdbmi]

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.

:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions

Syntax

sp_refreshview [ @viewname = ] 'viewname'
[ ; ]

Arguments

[ @viewname = ] 'viewname'

The name of the view. @viewname is nvarchar, with no default. @viewname can be a multipart identifier, but can only refer to views in the current database.

Return code values

0 (success) or a nonzero number (failure).

Remarks

If a view isn't created with SCHEMABINDING, sp_refreshview should be run when changes are made to the objects underlying the view, which affect the definition of the view. Otherwise, the view might produce unexpected results when it's queried.

Permissions

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.

Examples

A. Update the metadata of a view

The following example refreshes the metadata for the view Sales.vIndividualCustomer.

USE AdventureWorks2022;
GO

EXECUTE sp_refreshview N'Sales.vIndividualCustomer';

B. Create a script that updates all views that have dependencies on a changed object

Assume that the table Person.Person was changed in a way that would affect the definition of any views that are created on it. The following example creates a script that refreshes the metadata for all views that have a dependency on table Person.Person.

USE AdventureWorks2022;
GO
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');

Related content