Home Library Learn Downloads Support Community
Sign in | United States - English | |
2 out of 4 rated this helpful - Rate this topic

sp_refreshview (Transact-SQL)

SQL Server 2005

Updated: 14 April 2006

Updates the metadata for the specified non-schemabound view. Persistent metadata for a view can become outdated because of changes to the underlying objects upon which the view depends.

Topic link icon Transact-SQL Syntax Conventions

Copy 

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

Is 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.

0 (success) or a nonzero number (failure)

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.

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. Updating the metadata of a view

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

Copy 
USE AdventureWorks;
GO
exec sp_refreshview N'Sales.vIndividualCustomer';

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

Assume that the table Person.Contact 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.Contact.

Copy 
USE AdventureWorks;
GO
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('Person.Contact')

Release History

14 April 2006

New content:
  • Added the Remarks section to clarify the reason for using sp_refreshview.
  • Added example B.
Changed content:
  • Updated example A.
Did you find this helpful?
(1500 characters remaining)
Community Content Add
Annotations FAQ
Refresh all views example incomplete.
The example for the above is incomplete in that it does not take schema into account and produces literally sp_refreshview 'viewname' even if no view exists in dbo. To get the correct results, use:

SELECT 'EXEC sp_refreshview ''' + ss.name + '.' + so.name + ''''
FROM sys.objects so
INNER JOIN sys.sql_dependencies sd ON so.object_id = sd.object_id
INNER JOIN sys.schemas ss on ss.schema_id = so.schema_id
WHERE type = 'V'
AND sd.referenced_major_id = object_id('schema.TableName')
group by ss.name,so.name
History