SET NOEXEC and PARSEONLY: Checking a Script Without Running It

You want to catch a script error before it changes data. PARSEONLY checks syntax without compiling or executing the statement. NOEXEC adds compilation checks, but neither setting proves that the script will succeed when it finally runs.

Separate PARSEONLY Parsing From Compilation

Parsing checks whether the statement follows SQL grammar. Compilation also binds names and builds an execution plan where possible. Execution performs the work and encounters the real data, permissions, and resource conditions.

These are separate gates. A script passing the first gate hasn't passed the other two. Keep that distinction clear when reporting what a validation step established.

I use a fresh query window for these checks. Session settings can affect later statements, and a forgotten option makes a window confusing. The examples use GO to separate batches in SSMS.

GO is a client batch separator, not a statement sent to the engine. Use an equivalent batching approach in another supported client. Don't place the settings inside a stored procedure as a runtime validation scheme.

Check Syntax With SET PARSEONLY

Turn the parse setting on in its own batch. The following statement references a deliberately absent object, yet its grammar is valid. That illustrates the limit: PARSEONLY doesn't resolve the table or confirm its columns.

A misspelled keyword is a parsing problem. An inaccessible or nonexistent table belongs to a later stage. Don't treat those failures as interchangeable when reading an error message.

Turn the setting off afterward in its own batch too. Keep the reset visible at the end of the script. If you test a malformed statement, run the reset independently before continuing other work.

A dedicated window is easy to close if you aren't confident about its state. The engine shouldn't have to guess whether you intended the next query to execute.

SET PARSEONLY ON;
GO
SELECT MissingColumn
FROM dbo.DeliberatelyAbsentParseTable;
GO
SET PARSEONLY OFF;
GO

Compile Without Running the Statement

NOEXEC parses and compiles batches without executing them. It catches binding problems when the relevant metadata is available. For example, an invalid column on an existing table is different from a table deferred for later resolution.

Deferred name resolution still applies, so a missing table can escape this check. Don't promise that every absent table will generate an error under this setting.

Create the temporary test table before enabling NOEXEC. Then compile a valid SELECT against it. To explore binding errors, replace ItemId with an intentionally invalid column in a separate test.

I keep those deliberate failures out of deployment scripts. They demonstrate the checker's boundary. Keep intentional failures separate from the file someone will execute during maintenance.

CREATE TABLE #CompileCheck(ItemId int NOT NULL);
GO
SET NOEXEC ON;
GO
SELECT ItemId FROM #CompileCheck;
GO
SET NOEXEC OFF;
GO
SELECT COUNT_BIG(*) AS StoredRows FROM #CompileCheck;

Understand Deferred Name Resolution

A stored procedure can be created while a referenced table doesn't exist. The engine defers that object's resolution until later. That supports some deployment patterns, but it also lets a broken dependency survive creation.

A successful CREATE PROCEDURE isn't a complete integration test. The procedure still needs its objects and permissions when a caller executes the relevant statement.

The example below deliberately creates such a procedure in a disposable database. Its referenced table isn't created. Don't execute the procedure expecting a valid result. Inspect the dependency instead.

This makes the limitation visible without inventing a successful run. A grammar checker is useful, but it is not psychic. It cannot certify an object that hasn't been supplied to the environment.

CREATE PROCEDURE dbo.DeferredReferenceDemo
AS
BEGIN
    SELECT ItemId FROM dbo.DeliberatelyAbsentDependency;
END;
GO

Inspect Existing Module References

The sys.dm_sql_referenced_entities function resolves referenced entities for an existing module under the current environment. It can expose incomplete bindings or raise a dependency error. Read that error as evidence to investigate, not as an instruction to alter the module immediately.

The function needs suitable metadata access. Dynamic SQL and references assembled at runtime aren't comprehensively described by a static dependency inventory.

For the demo procedure, the function returns one row naming dbo.DeliberatelyAbsentDependency, even though that table doesn't exist. A listed name isn't proof that the object is there. The TRY/CATCH block keeps the check readable if another module raises a dependency error instead.

For real modules, inspect the referenced server, database, schema, entity, and ambiguity information. Check the target environment after deployment, because a source environment can contain dependencies the target lacks. A dependency list should be tied to the database context and collection time under which it was produced.

BEGIN TRY
    SELECT referenced_schema_name,referenced_entity_name,
           referenced_minor_name,is_ambiguous
    FROM sys.dm_sql_referenced_entities(N'dbo.DeferredReferenceDemo',N'OBJECT');
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS DependencyError,ERROR_MESSAGE() AS DependencyMessage;
END CATCH;

Include the Runtime Conditions

Neither setting evaluates a CHECK constraint against new business data or proves that a conversion will succeed for every row. Permissions, blocking, disk space, and transaction behavior also remain runtime concerns. A script can compile and then fail when a row contains an invalid value. Keep data validation and a controlled rehearsal in the release process after the static checks have passed.

Which part of this script can only be proved with representative data? Identify it before declaring the review complete. DDL order and database context matter too.

A module checked against yesterday's schema can break after today's column change. Re-run the relevant dependency checks after the actual deployment. The validation result belongs to a particular environment, not to a timeless copy of the script.

Reset PARSEONLY and NOEXEC, Then State the Result

Use a separate validation connection and close it when the checks finish. In an existing window, reset both options explicitly before other work. The read-only nature of the checked statements doesn't make session-state changes invisible.

Keep ON and OFF batches together in the review copy. Verify the reset after any deliberate syntax error interrupts execution.

Use PARSEONLY for grammar and NOEXEC for additional compilation feedback, with deferred resolution understood. Follow with dependency inspection and an isolated execution rehearsal where appropriate. Report which gate passed and which remains untested.

That gives the next operator useful evidence. A quiet query window is encouraging, but it doesn't establish that a future deployment has already succeeded.

Check the intended database name before each validation pass. A table existing in another open connection doesn't satisfy this session's context. Use an explicit context in the deployment plan and preserve the target identity with the result. That turns an otherwise vague compilation success into evidence tied to the database you actually checked.

Related reading on this blog: What Breaks If I Drop This Column? sys.sql_expression_dependencies and What Does SET NOEXEC Do?.

A script check is not a successful deployment, it is evidence from one validation stage.

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