sp_refreshsqlmodule: Refreshing Views and Procedures After a Table Change

The table changed, but the view still describes yesterday's columns. Use sp_refreshsqlmodule to refresh dependent metadata after reviewing the change and its callers.

When Stored Metadata Falls Behind

A module definition and its stored metadata serve different purposes. The definition contains your SQL text. The metadata describes names, types, and other details SQL Server retained when the module was created.

Changing an underlying table doesn't rewrite every dependent module. A view built with SELECT * is a familiar example. Its saved column list can lag behind the table's current shape.

I check view definitions before treating a strange result as damaged data. I also check the caller's expected column order. A report can fail even when each value still belongs to the correct table.

Procedures and functions deserve the same review after relevant type changes. Refreshing metadata doesn't repair an invalid expression. A reference to a removed, explicitly named column still needs a code change.

Reproduce the Shift in a Test Database

Use an empty test database for these examples. The objects have distinctive names, but the table changes are deliberate. Don't point this script at a production table.

The fixture starts with two integer attributes after an identifier. Dropping the first attribute and appending another changes their physical order. Compare table columns with the view's saved labels before querying it.

CREATE TABLE dbo.RefreshSource
(
    ItemId int NOT NULL PRIMARY KEY,
    LeftValue int NOT NULL,
    RightValue int NOT NULL
);
INSERT dbo.RefreshSource VALUES (1, 10, 20);
GO
CREATE VIEW dbo.RefreshView
AS
SELECT * FROM dbo.RefreshSource;
GO
SELECT * FROM dbo.RefreshView;
ALTER TABLE dbo.RefreshSource DROP COLUMN LeftValue;
ALTER TABLE dbo.RefreshSource ADD ExtraValue int NOT NULL
    CONSTRAINT DF_RefreshExtra DEFAULT (30);
SELECT OBJECT_NAME(object_id) AS ObjectName, column_id, name
FROM sys.columns
WHERE object_id IN
    (OBJECT_ID(N'dbo.RefreshSource'), OBJECT_ID(N'dbo.RefreshView'))
ORDER BY ObjectName, column_id;
SELECT * FROM dbo.RefreshView;

Read the result labels alongside the catalog output. The unbound view retains the earlier column metadata until refreshed. In my run, the view still labeled 20 as LeftValue and 30 as RightValue. The changed ordering demonstrates why a successful query alone doesn't prove a stable result contract.

The numbers are synthetic inputs, not measured findings from a server. Run the example and inspect its output yourself. A column label has no conscience about yesterday's meaning.

Check columns with different types as a separate test case. A stale label paired with a shifted integer is misleading. A shifted conversion can fail instead, depending on the definition.

Record the result shape before the table alteration. Include every column name and its ordinal position. That reference makes the intended interface easier to compare with the refreshed version.

Refresh the View With sp_refreshsqlmodule

Use the view-specific procedure when the object under review is a view. It updates the metadata for a view without schema binding. Run the catalog query again afterward to inspect the current names.

EXEC sys.sp_refreshview @viewname = N'dbo.RefreshView';
SELECT * FROM dbo.RefreshView;

EXEC sys.sp_refreshsqlmodule @name = N'dbo.RefreshView';
SELECT column_id, name, TYPE_NAME(user_type_id) AS TypeName
FROM sys.columns
WHERE object_id = OBJECT_ID(N'dbo.RefreshView')
ORDER BY column_id;

For views, sp_refreshsqlmodule provides the same refresh result. It also handles other supported SQL modules without schema binding. Choose the command that fits the reviewed object and change process.

Refresh doesn't restore the removed attribute or preserve the earlier contract. This view now follows the table's current column list. Callers expecting LeftValue still need attention before the change is accepted.

Avoid SELECT * in published interfaces. Explicit columns make the intended output visible in the definition. They also turn a missing dependency into a clear failure requiring a deliberate repair.

List What sp_refreshsqlmodule Should Touch

The dependency catalog records references made by persisted SQL expressions. Join it to SQL modules to find direct dependents of this table. Remove duplicates because dependency rows can describe more than one referenced column.

DECLARE @TableId int = OBJECT_ID(N'dbo.RefreshSource', N'U');
IF @TableId IS NULL
    THROW 51000, 'The source table was not found.', 1;
SELECT DISTINCT o.object_id,
    QUOTENAME(OBJECT_SCHEMA_NAME(o.object_id)) + N'.' + QUOTENAME(o.name)
        AS ModuleName,
    o.type_desc
FROM sys.sql_expression_dependencies AS d
JOIN sys.objects AS o ON o.object_id = d.referencing_id
JOIN sys.sql_modules AS m ON m.object_id = o.object_id
WHERE d.referenced_id = @TableId
  AND d.referencing_class = 1
  AND m.is_schema_bound = 0
  AND o.type IN ('V', 'P', 'FN', 'IF', 'TF', 'TR');

This list covers resolved, direct references within the current database. It doesn't discover every name assembled inside dynamic SQL. References through another view also require a separate review of that dependency chain.

Cross-database references retain names without a resolved referenced_id here. Metadata visibility also depends on the caller's permissions. An empty list under a restricted account is insufficient evidence that nothing depends on the table.

Save the dependency list while the original objects still exist. Dropping and recreating objects changes their identifiers. Treat that operation as a separate change requiring fresh dependency discovery.

Avoid assuming that a procedure selecting through a view appears as a direct table dependent. Follow the intermediate object's references too. Refresh and test those layers in dependency order.

Run a Reviewed sp_refreshsqlmodule Loop

Module signatures require special care. Running sp_refreshsqlmodule drops signatures associated with the refreshed object. That can change permission behavior when execution depends on certificate signing.

The loop below refuses signed modules instead of silently stripping their signatures. Production work needs an approved signing and verification process. Quoted, schema-qualified names also prevent ambiguous object selection.

DECLARE @TableId int = OBJECT_ID(N'dbo.RefreshSource', N'U');
IF @TableId IS NULL
    THROW 51001, 'The source table was not found.', 1;
DECLARE @ModuleId int, @ModuleName nvarchar(517);
DECLARE @Failures TABLE (ModuleName nvarchar(517), FailureText nvarchar(4000));
DECLARE RefreshModules CURSOR LOCAL FAST_FORWARD FOR
SELECT DISTINCT o.object_id,
    QUOTENAME(OBJECT_SCHEMA_NAME(o.object_id)) + N'.' + QUOTENAME(o.name)
FROM sys.sql_expression_dependencies AS d
JOIN sys.objects AS o ON o.object_id = d.referencing_id
JOIN sys.sql_modules AS m ON m.object_id = o.object_id
WHERE d.referenced_id = @TableId
  AND d.referencing_class = 1 AND m.is_schema_bound = 0
  AND o.type IN ('V', 'P', 'FN', 'IF', 'TF', 'TR');
OPEN RefreshModules;
FETCH NEXT FROM RefreshModules INTO @ModuleId, @ModuleName;
WHILE @@FETCH_STATUS = 0
BEGIN
    BEGIN TRY
        IF EXISTS (SELECT 1 FROM sys.crypt_properties
                   WHERE class = 1 AND major_id = @ModuleId)
            THROW 51002, 'Signed module requires an approved signing process.', 1;
        EXEC sys.sp_refreshsqlmodule @name = @ModuleName;
    END TRY
    BEGIN CATCH
        INSERT @Failures VALUES (@ModuleName, ERROR_MESSAGE());
    END CATCH;
    FETCH NEXT FROM RefreshModules INTO @ModuleId, @ModuleName;
END;
CLOSE RefreshModules;
DEALLOCATE RefreshModules;
SELECT ModuleName, FailureText FROM @Failures;
IF EXISTS (SELECT 1 FROM @Failures)
    THROW 51003, 'At least one module was not refreshed.', 1;

Review the selected objects before adapting this loop to another table. The account needs permission to alter each module. A refresh failure belongs in the change record, followed by a specific repair.

Successful refresh calls don't mean the application accepted a new result shape. Driver mappings, exported files, and downstream expressions all carry assumptions. Test those assumptions using representative calls.

If a failure identifies an invalid reference, fix the definition explicitly. Don't hide that failure by deleting a dependency row. Catalog entries describe dependencies that belong to the SQL definition.

Use Schema Binding for a Stable Interface

WITH SCHEMABINDING prevents underlying changes that would invalidate the bound definition. A bound view uses explicit columns and two-part object names. Its dependencies stay within the same database.

GO
CREATE VIEW dbo.RefreshBoundView
WITH SCHEMABINDING
AS
SELECT ItemId, RightValue FROM dbo.RefreshSource;
GO
SELECT ItemId, RightValue FROM dbo.RefreshBoundView;

A change to a referenced column now requires addressing the bound object first. That protection suits interfaces whose shape must stay controlled. It also adds coordination when table changes are frequent.

I prefer that visible dependency when an application relies on fixed output. Still, binding isn't a replacement for caller tests. Which report, procedure, or application will notice a changed type first?

Schema binding doesn't freeze every property of the underlying table. Unreferenced additions still require their own operational review. Distinguish protection of the view's dependencies from a ban on all table changes.

Removing binding to force an alteration discards that protection. Review the replacement definition before doing so. A coordinated change should end with a stable, documented interface again.

Finish With Caller Evidence

Compare result names, types, nullability, and order after the refresh. Exercise the inputs that callers use. Include any execution context affected by signing or impersonation.

Keep the previous definitions and the intended table change together for review. Use sp_refreshsqlmodule as one step in that change. Accept the change only after the refreshed modules and their callers agree.

Related reading on this blog: Stale Views: Why SELECT * Breaks After ALTER TABLE and What Breaks If I Drop This Column? sys.sql_expression_dependencies.

A metadata refresh is not a repaired interface, it is a renewed description of the current definition.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.