Fix “Msg 207: Invalid Column Name” in SQL Server

What Msg 207 Says

Check the Object’s Actual Columns

Before guessing, check what columns the object actually has:

SELECT c.name
FROM sys.all_columns c
JOIN sys.all_objects o ON o.object_id = c.object_id
WHERE o.name = 'the_view_or_table_name'
ORDER BY c.column_id;

For a single column, COL_LENGTH('schema.object', 'column') returns NULL when it is not there and works for DMVs as well as tables. For a DMV, sys.dm_exec_describe_first_result_set against a minimal SELECT * FROM <dmv> also works. Microsoft’s reference covers MSSQLSERVER_207 and lists every column of the system dynamic management views, worth a direct check when the view name is unfamiliar.

The column list tells you what is there. It does not tell you why the one you wanted is not, and that is what decides the fix. Almost all of it comes down to 3 causes, and each takes a different fix. Find the row that matches what you are looking at.

Every example below is a real error the dba-tools repo hit while it was being built, not invented for this post. Each row is the column as it was first written, where it actually lives, and the fix that shipped. They are grouped by the pattern behind the mistake, because the pattern is what carries over to your own query.

The Column Belongs to a Different Object

The most common cause by a distance. A handful of catalog views describe related parts of one thing, and the column you want lives on a different one from the view you queried: it belongs to the object’s module or its running state rather than its definition, or it only makes sense for one subtype and so lives on the narrower view for that subtype. The tell is that no amount of checking spelling or adding a cast helps, because the name is right and the object is wrong. Ask which view owns the concept, then join to it.

The fix has the same shape every time: stay on the view you started from, join the view that owns the column, and let the join return NULL where the concept does not apply. The login audit, as it shipped:

SELECT sp.name, sp.type_desc,
       sl.is_policy_checked, sl.is_expiration_checked
FROM sys.server_principals AS sp
LEFT JOIN sys.sql_logins AS sl ON sl.principal_id = sp.principal_id;

The Column Exists Under Another Name

The object is right and the column is on it, but it is not spelled the way the obvious guess would spell it: a plural where the real name is singular, a missing suffix, a has_ where you expected is_. No rule predicts which, and a view can be inconsistent with itself (has_filter and is_ms_shipped sit side by side on sys.stats). The fix is always the same: read the real column list, use the real name, and alias it if the rest of your script depends on the old one.

Version Guards Cannot Prevent Compile-Time Errors

A script was gated on IF CAST(SERVERPROPERTY('ProductMajorVersion') AS INT) < 13 before selecting total_spills from sys.dm_exec_query_stats, with a friendly “this needs a newer version” message in the other branch. On an instance without that column the friendly message never appeared. The batch failed with Msg 207 instead, and the IF was never evaluated at all.

The reason is binding. SQL Server resolves column references for the whole batch at compile time, before a single statement executes. A runtime IF cannot protect a statically written column reference, because compilation has already failed by the time the branch would be chosen. Missing objects behave differently, which is what makes this so confusing: deferred name resolution means a missing table is only resolved when the statement actually runs, so the identical guard around a missing table appears to work while the same guard around a missing column does not.

You can watch both halves of that on any instance:

-- Msg 207, even though the branch never runs
IF 1 = 0
    SELECT no_such_column FROM sys.databases;
ELSE
    SELECT 'guard worked' AS info;
GO

-- prints 'guard worked': the dynamic batch is not bound unless it runs
IF 1 = 0
    EXEC sys.sp_executesql N'SELECT no_such_column FROM sys.databases;';
ELSE
    SELECT 'guard worked' AS info;

The fix is to detect the column rather than the version, and keep the guarded query in dynamic SQL so it is only bound when it can succeed:

IF COL_LENGTH('sys.dm_exec_query_stats', 'total_spills') IS NULL
BEGIN
    SELECT 'This build does not expose total_spills.' AS info;
END
ELSE
BEGIN
    DECLARE @sql nvarchar(max) = N'
    SELECT TOP (30) qs.total_spills, qs.max_spills, qs.execution_count
    FROM sys.dm_exec_query_stats AS qs
    WHERE qs.total_spills > 0
    ORDER BY qs.total_spills DESC;';

    EXEC sys.sp_executesql @sql;
END;

Feature detection is also more accurate than a version number, and by a wider margin than people expect. total_spills arrived in SQL Server 2016 SP2 and SQL Server 2017 CU3, not in 2016 RTM, so a ProductMajorVersion >= 13 test is wrong at both ends: it lets 2016 RTM and 2016 SP1 through to fail, and it blocks nothing useful. COL_LENGTH asks the only question that matters, which is whether this build has the column.

One more thing worth knowing, because it turns this class of bug into something you catch before shipping. SET NOEXEC ON compiles and binds a batch without executing it, so it raises Msg 207 on a bad column name in seconds. SET PARSEONLY ON does not: it checks syntax only, and SELECT no_such_column FROM sys.databases passes it cleanly. If you validate scripts before release, NOEXEC is the setting you want.

Verify Catalog Columns on the Target Instance

• When a query fails with Msg 207 against a DMV or catalog view you’re not intimately familiar with, check the real column list before guessing at a fix. A guessed fix that happens to compile can still be wrong in a different way.
• Treat views with similar names (sys.dm_hadr_availability_replica_states vs. sys.dm_hadr_database_replica_states, sys.server_principals vs. sys.sql_logins) as genuinely different data, not variants of the same thing.
• When testing a new script against a DMV, run it against a real instance before trusting it. Several of the errors above only surfaced when the script was actually executed against SQL Server, not from reading the T-SQL alone.
• Keep a note of which DMV owns which column once you’ve had to look it up; the same near-miss naming trips people up repeatedly across different scripts touching the same subject area.

Scripts With Real Msg 207 Fixes

Every real example above is fixed and live in the repo behind these posts:

• Get Audit Specifications, DDL Triggers, and Proxy Credentials
• High Availability (pillar), includes AG Replica Role and Synchronization State
• Get Lock Escalation, Contention Analysis, and Blocking Chains with Plan
• Get Migration Login Audit and Post-Migration Validation
• Get Trace Flags and Resource Governor Configuration
• Get Statistics Health
• Generate Linked Server Script

Frequently Asked Questions

Where To Go Next