Per the comment, one option is to add set noexec on to the top of the query window. This setting persists across batches. It is evaluated at execution time and can therefore be run conditionally (unlike many other set statements).
As noted by Randy in Marin, this is still not completely safe because the script could contain a set noexec off. If you set noexec on SQL will still execute the set noexec off (obviously, otherwise there wouldn't be a way to turn it off!), and then any subsequent statements would be executed.
Another option - and possibly an even better one - would be set parseonly on.
One difference between the two is that with set parseonly on The engine will literally do only that - parse the syntax. It won't actually do any "work". With set noexec on any statements will still produce plans, but the plans won't be executed. So set parseonly on is "cheaper" than set noexec on.
The other difference is that set parseonly on cannot be executed conditionally. That is to say, a line of code like if (1 = 0) set parseonly on will result in parseonly being set to "on", because the if is evaluated at execution time, but for obvious reasons parseonly is not evaluated at execution time, because that would defeat the point!
And another difference: while parseonly will persist across batches, only one parseonly within each individual batch counts, and it's the last one. For example:
set parseonly on;
select 'hello';
set parseonly off;
go
This will return the result set "hello", because there is a parseonly off in the batch, and it is the last parseonly setting in the batch.
And of course, even with parseonly a similar danger applies: If the script has a set parseonly off, then some statements can still get executed. Not just the statements following, but even other statements that precede it in the same batch, if it is the last setting for parseonly in that batch.
Is there anything else you can do? Yes. Enter sqlcmd. The :on error exit sqlcmd directive will tell sqlcmd to stop executing anything if any kind of error occurs - batch terminating or otherwise.
What we do in our deployment scripts is this:
:on error exit
set xact_abort on;
begin tran;
-- migration script content here
commit;
You can do something similar here to avoid execution of anything in the script in a way that has no danger of being turned off, you can put the query window into sqlcmd mode, and put this at the top:
:on error exit
throw;
-- rest of script here
Now, the throw here won't actually throw an error, since we're not in a catch block. In fact, it is an error to have this throw here. But... that's all we need. The error will cause the :on error exit to terminate all further execution of the file. You could also just have a raiserror (...) instead, but that means more typing :P
Is this now a guaranteed solution? No, because what if you forget to put your window into sqlcmd mode? You can set windows to open in sqlcmd mode by default... but what if you turn it off? The first batch will fail (syntax error since the sqlcmd syntax won't be valid), but subsequent batches will execute.
You can of course combine both methods...
:on error exit
throw;
go
set noexec on;
-- ... rest of script with many batches
But as already described, its still possible for statements to be executed if you are not in sqlcmd mode and there are set noexec off statements anywhere in the script.
