How to test SQLCMD on a SQL script without executing the script

SET NOEXEC ON makes SQL Server enter a mode where statements are parsed and compiled but not executed, with the exception of the command SET NOEXEC OFF, which reverts things back to their original state. So, if statements end up syntactically invalid because of missing parameters, SET NOEXEC ON will catch it.

There is an important limitation to SET NOEXEC ON. The documentation misleadingly states that "this setting is useful for having SQL Server validate the syntax and object names in Transact-SQL code when executing", but, in fact, under SET NOEXEC ON, SQL Server will not resolve names to most objects at all. The following script will parse without error:

SET NOEXEC ON;
GO
CREATE TABLE Foo(ID INT PRIMARY KEY);
GO
INSERT Fooo(ID) VALUES (5);  -- Oops, typo

That doesn't mean that no names are resolved at all. The following script will still fail:

SET NOEXEC OFF;
GO
CREATE FUNCTION Foo() RETURNS TABLE AS RETURN (SELECT 0 Bar);
GO
SET NOEXEC ON;
GO
SELECT dbo.Foo();  -- No error here, even though Foo can't be invoked this way
SELECT * FROM dbo.Foo(5);  -- Error here: too many arguments specified

Fair enough, you might say, but:

SET NOEXEC ON;
GO
CREATE FUNCTION Foo() RETURNS TABLE AS RETURN (SELECT 0 Bar);
GO
SELECT * FROM dbo.Foo(5);  -- No error, because Foo doesn't exist

So SET NOEXEC ON can serve neither as a way to guarantee a script is completely valid, nor as a way to determine a script is definitely invalid. As long as you're aware of its limitations it can still be useful.

There is another setting that does work consistently as far as object names go: SET PARSEONLY ON. This not only doesn't execute statements, it doesn't even compile them. The example above which gives an error with NOEXEC will give no error with PARSEONLY. As does the following:

CREATE TYPE MyInt FROM INT;
GO
CREATE TABLE A(ID MyInt);  
  -- Error under NOEXEC since MyInt does not exist, no error under PARSEONLY

So if all you want to check is syntactical validity, PARSEONLY is better than NOEXEC.