A table changes, but a view defined with SELECT * keeps an old idea of its columns. Stale views can return missing or shifted columns until their metadata is refreshed.
See How Stale Views Happen
SQL Server stores view metadata when the view is created. A non-schema-bound view using SELECT * can keep a column list that no longer matches the base table after ALTER TABLE. The view text still says star, which makes the mismatch confusing. I reproduce it in a test database before touching a real view. Adding or dropping a column is enough to show why callers should not depend on a star in an interface object. The exact visible symptom depends on the change and query shape.
CREATE TABLE dbo.ViewDemo (ID int NOT NULL, Label varchar(20) NOT NULL);
GO
CREATE VIEW dbo.ViewDemoAll AS SELECT * FROM dbo.ViewDemo;
GO
ALTER TABLE dbo.ViewDemo ADD Category varchar(20) NULL;
SELECT * FROM dbo.ViewDemoAll;Find Likely Stale Views
Search sys.sql_modules for SELECT followed by a star in view definitions. Text matching is a triage tool, not a SQL parser. Formatting, aliases, comments, and dynamic forms can produce misses or false positives. Review each candidate definition and its dependencies. I also check views that name all columns but reference changed base types, because metadata can need refresh after other table changes. The search narrows the work; it does not certify safety.
SELECT s.name AS schema_name, v.name AS view_name, m.definition
FROM sys.views AS v
JOIN sys.schemas AS s ON s.schema_id = v.schema_id
JOIN sys.sql_modules AS m ON m.object_id = v.object_id
WHERE m.definition LIKE N'%SELECT%*%'
ORDER BY s.name, v.name;Refresh One View Deliberately
sp_refreshview updates metadata for a non-schema-bound view after its underlying objects change. Run it for the affected view, then compare columns and application behavior. Do not run a blind refresh across every view during peak traffic. A refresh can expose dependencies and permission issues that need review. I capture the prior metadata and definition so the change is explainable. If the view feeds reports that map columns by ordinal, test them carefully; shifted ordinals can be worse than an obvious error.
EXEC sys.sp_refreshview N'dbo.ViewDemoAll';
SELECT name, column_id, system_type_id
FROM sys.columns
WHERE object_id = OBJECT_ID(N'dbo.ViewDemoAll')
ORDER BY column_id;Replace Star With a Contract
List the columns explicitly in the view definition and give the result a deliberate order of columns. New base-table columns will not automatically appear, which is usually the safer behavior for an application interface. I treat the view like an API: adding a field should be an intentional versioned change. SELECT * is convenient while exploring data, but it creates hidden coupling when saved as a view. It also reads columns a caller does not need if the optimizer cannot eliminate them in a particular query shape.
Review downstream consumers before changing the view's columns. A report that uses SELECT * from the view can still have its own fragile column-order assumption.
Consider SCHEMABINDING
SCHEMABINDING prevents incompatible base-table changes while a view depends on those columns. It requires explicit column references and other rules, so it is a stronger contract for stable interfaces. I use it when the schema relationship deserves that protection, not as a universal flag. A schema-bound view can make planned table changes require coordinated view updates first. That friction is useful when it stops an accidental break, but it belongs in the deployment plan.
What should happen when a new table column arrives? If the answer is "the view stays the same until reviewed," explicit columns are enough. If the answer is "block incompatible changes," schema binding adds that gate.
Inspect the View's Public Shape
Before refreshing a view, list its columns from sys.columns and compare them with the base table and with the application's expected result. A refresh can expose a newly added column or alter an ordinal in a SELECT * view. Callers that bind by position can break even when their query still runs. I test representative reports and APIs after the refresh, including cached metadata in data access layers. Some clients prepare statements or generate models from view columns; they can need their own refresh or deployment.
I keep the old and new column lists with the change record. If a dropped base column was previously exposed, decide what replaces it for consumers. A metadata refresh is not a substitute for an interface migration plan. It makes the view truthful, which can reveal a contract violation that had been hidden.
Add a Dependency Check to DDL Review
When a table changes, query its dependent views and modules before deployment. Review both explicit dependencies and definitions that use dynamic SQL or cross-database references, since dependency metadata is not always complete. I search for SELECT * in saved views as a design smell and replace it during a planned change rather than after a production surprise. For stable interfaces, list columns explicitly and add tests that assert names and types.
What happens if a base column type changes but the view still reports the old metadata? Run sp_refreshview on affected non-schema-bound views in a test copy and inspect the new shape. If SCHEMABINDING blocks the base change, that is an intentional review gate: update the view and its consumers in the right order. I prefer a deployment that fails clearly over a view that keeps a stale column map and returns a misleading result.
If the view is used by an ORM or reporting tool, refresh its model metadata after the SQL view is corrected. The database can expose the right columns while a cached client model still expects the old shape. I test through the application after sp_refreshview and explicit-column changes. A successful SELECT in SSMS is only one layer of the contract.
Check for Stale Views After Every Table Change
Include dependent views in the table-change checklist. Query sys.columns for the view and base table, run a representative SELECT, and test consumers. I prefer a deployment script that refreshes only affected non-schema-bound views and records the result. After the demo, clean up test objects in the test database. Do not assume a successful ALTER TABLE means every saved view now describes the new schema.
The best prevention against stale views is to stop putting stars in stable view definitions. Refresh repairs today's mismatch. Explicit contracts prevent the next one.
Related reading on this blog: How to STOP the Usage of SELECT * For Views? Interview Question of the Week #193 and View Dependencies on SQL Server: Hard & Soft Way.
SELECT * in a view is not a live column list, it is a column list saved when the view was created.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
