Troubleshooting Guide: SQL Server Error 5370 — Invalid Column Reference in View

Why This Error Happens

This error occurs when the underlying schema of a base table referenced by a view has changed (such as columns being added, dropped, renamed, or reordered) after the view was originally created, causing the view’s cached metadata to become stale. SQL Server stores column binding information at view creation time, and when this binding no longer matches the actual table structure, the engine cannot resolve the column references correctly. This is particularly common after schema migrations, ALTER TABLE operations, or database restores where views were not refreshed alongside structural changes.

Step-by-Step Resolution Guide

Step 1: Identify the Affected View and Base Table

• Open SQL Server Management Studio (SSMS) and connect to the instance reporting the error.
• Run the following query to capture the exact view name from the error message:

SELECT * FROM sys.views WHERE name = 'YourViewName';

• Cross-reference the view definition using sp_helptext 'YourViewName' to identify which base table columns are referenced.

Step 2: Refresh the View Metadata Using sp_refreshview

• Execute the built-in system stored procedure to force SQL Server to re-evaluate and re-bind the view’s column metadata:

EXEC sp_refreshview 'dbo.YourViewName';

• If the database contains multiple views affected by the same base table change, loop through them using a cursor or run sp_refreshview individually for each.
• Check for error output; if sp_refreshview itself fails, this indicates deeper schema mismatches requiring manual recreation (proceed to Step 3).

Step 3: Manually Drop and Recreate the View (If Refresh Fails)

• Script out the original view definition using SSMS (Right-click view → Script View as → CREATE To).
• Drop the broken view:

DROP VIEW dbo.YourViewName;

• Recreate it with the corrected column list matching the current base table schema:

CREATE VIEW dbo.YourViewName AS
SELECT Column1, Column2, Column3
FROM dbo.BaseTable;

• Ensure any SELECT * usage in the original view is replaced with explicit column names to prevent recurrence after future schema changes.

Step 4: Audit Related Dependent Objects

• Use sys.sql_expression_dependencies to check if other views, stored procedures, or functions depend on the view you just fixed:

SELECT * FROM sys.sql_expression_dependencies 
WHERE referenced_entity_name = 'YourViewName';

• Refresh any dependent objects using the same sp_refreshview or sp_refreshsqlmodule (for stored procedures/functions) to prevent cascading errors.

Alternative or Verification Steps

• Verify the fix by querying the view directly (SELECT TOP 10 * FROM dbo.YourViewName;) and confirming no error 5370 is thrown and data returns as expected.
• If sp_refreshview consistently fails due to encryption (WITH ENCRYPTION views) or schema binding conflicts, use SSMS Generate Scripts Wizard to export the full view definition, drop it, and recreate it manually with updated column references as outlined in Step 3.