How can I run sp_refreshview across all views in my SQL Server database

Question

I'm using SQL Server, and I received an error indicating that one or more of my SQL views is out of sync with its underlying SQL table.

'MySQLServerName' returned data that does not match expected data length for column 'MyColumnName'. The (maximum) expected data length is 50, while the returned data length is 52.

This occurs if you update the definition of the underlying SQL table, but forget to update the associated SQL view(s). A quick fix for this error is to run sp_refreshview:

use MySQLDatabaseName
go
EXECUTE sp_refreshview N'MyViewName';

But what if I have a long list of broken views, or I don't even know which views are broken, or I don't know which underlying table definitions are no longer matching those views? What if I want to save time by running sp_refreshview across every view in my database. What's an easy way to accomplish that task?

Speedcat · Accepted Answer · 2024-10-24T02:30:54.913

I'll get to my solution in a minute.

But first off, we can avoid this problem altogether. I'd like to tip my hat to Aaron Bertrand, who helped us understand the benefit of using WITH SCHEMABINDING when creating a SQL Server view. From Aaron:

You can use WITH SCHEMABINDING on the views, which will explicitly enforce that you can't modify referenced objects without knowingly removing the schema binding. (As an added bonus, your views can't use SELECT *, which is likely part of the problem here).

I wrote about this in the following article:

Benefits of SCHEMABINDING

To answer the original question, you can easily run sp_refreshview across all views in your database by merely executing the following statement:

USE [MyDatabaseName]
GO
EXECUTE sp_MSforeachtable 
@command1 = 'sp_refreshview [?];', 
@whereand = 'and OBJECTPROPERTY(o.id, N''IsUserTable'') = 0 
or OBJECTPROPERTY(o.id, N''IsView'') = 1'

Here's some information on sp_MSforeachtable and how you can extend it to iterate through different SQL Server objects. Please note that sp_MSforeachtable is an undocumented system stored procedure, so it could change at any time. So far I've tested it successfully on SQL Server 2019.

Happy SQL Server maintenance, y'all!

Charlieface · Answer 2 · 2024-10-22T20:36:29.090

It seems wasteful to refresh all the views. You only need to refresh views which access that table. Also, you need to refresh procedures, triggers and functions, which you can do with sp_refreshsqlmodule (which also covers views).

You can use the following script to automate it. This will use sys.sql_expression_dependencies to get all references to the relevant table, and refresh those modules.

DECLARE @yourTable nvarchar(1000) = N'dbo.t';  -- CHANGE THIS LINE
DECLARE @sql nvarchar(max) = (
    SELECT STRING_AGG(N'
EXEC sp_refreshsqlmodule N''' + QUOTENAME(s.name) + '.' + QUOTENAME(o.name) + ''';',
      '')
    FROM sys.objects o
    JOIN sys.schemas s ON s.schema_id = o.schema_id
    WHERE o.object_id IN (
        SELECT ed.referencing_id
        FROM sys.sql_expression_dependencies ed
        WHERE ed.referenced_id = OBJECT_ID(@yourTable)
    )
      AND o.type IN ('P', 'V', 'FN', 'IF', 'TF', 'TR')
);
PRINT @sql;
EXEC sp_executesql @sql;

How can I run sp_refreshview across all views in my SQL Server database

2 Answers2