How to prevent views with outdated metadata?

SQL Server is persisting various metadata when a new view is created in SQL Server. When underlying objects are changed later then view metadata aren’t updated automatically and this outdated metadata can cause several issues later. Let’s see one simple example. We will create a dbo.SampleTable table and create view v_Test referencing this table. Then we will create two other views on top of each one of them.

-- Create sample table
CREATE TABLE dbo.SampleTable
(
    Id VARCHAR(10)
)
GO

-- Create views for testing
CREATE VIEW dbo.v_Test
AS
    SELECT Id FROM dbo.SampleTable
GO

CREATE VIEW dbo.v_Test2
AS
    SELECT Id FROM dbo.v_Test
GO

CREATE VIEW dbo.v_Test3
AS
    SELECT Id FROM dbo.v_Test2
GO

Let’s check metadata persisted at views creation:

SELECT 
    [o].[name], [t].[name] [TypeName], [c].[precision], [c].[scale]
FROM [sys].[columns] [c]
    INNER JOIN [sys].[objects] [o] ON [o].[object_id] = [c].[object_id]
	INNER JOIN [sys]. [t] ON [t].[system_type_id] = [c].[system_type_id] AND [t].[system_type_id] = [c].[system_type_id]
WHERE [o].[name] IN ('SampleTable', 'v_Test', 'v_Test2', 'v_Test3')
ORDER BY [o].[name]
GO

All views have it’s own metadata records in system tables saying that the underlying object column has VARCHAR data type. Le’ts go and change the data type in dbo.SampleTable from VARCHAR to INT and query metadata again.

ALTER TABLE dbo.SampleTable
    ALTER COLUMN Id INT
GO

Metadata are inconsistent now: Our base table has a column of INT datatype but all views on top of it are still holding the old information that it was VARCHAR previously. What to do now? Sure we can drop and recreate all views. But there is a better option: Let’s call the sp_refreshview system stored procedure and refresh the metadata of the v_Test view.

EXEC sp_refreshview 'v_Test'
GO

v_Test view has correct metadata now. But it’s obvious that we must do the same action for all remaining views.

EXEC sp_refreshview 'v_Test2'
GO

EXEC sp_refreshview 'v_Test3'
GO

Our database schema is consistent now. Congratulation. But isn’t it too much unpredictability and overhead to stay consistent? I can’t imagine application developers to take care of it when they are building tables. Most of the time they know zero about database metadata. So what are your options as DBA to prevent this to happen?

I see forced usage of the WITH SCHEMABINDING clause as the most reliable option. See more on how it works in this article. Yes, developers will complain all the time they have more work to do and when there is a huge structure of view maintenance of scripts can become complicated. But all that assures that zero errors related to outdated views metadata will occur by your customer. I will call this as a best practice and you can enforce it in your CI/CD pipeline.

Another option is that you will simply script refresh view on every view changed or run some generic script to refresh all views after each release. There is such a sample one:

DECLARE @Cnt INT = 0
DECLARE @Runs INT = 5 -- views will be updated up to this nested level
DECLARE @Stmt NVARCHAR(MAX) = ''

WHILE @Cnt < @Runs
BEGIN

	SELECT @Stmt = @Stmt + 'EXEC sp_refreshview ''' + s.[name] + '.' + v.[name] + ''';' + CHAR(13)
	FROM sys.[views] v
		INNER JOIN sys.[schemas] s ON [s].[schema_id] = [v].[schema_id]

	PRINT @Stmt

	EXEC (@Stmt)

	SET @Cnt = @Cnt + 1

END

It will load all views from the database and script EXEC sp_refreshview for each of them. It will repeat this until @Runs level is reached. This will handle the situation when views are nested and you should adjust it to your maximum known level of nesting. This is a brute-force solution but it works with minimum effort. Sure we can script it more in a more advanced way i.e. using sys.sql_expression_dependencies system view. I will try to write such a script for you later.