Modifying the “New Query” template.
13June 11, 2015 by Kenneth Fisher
TLDR; Search for and modify the sqlfile.sql file.
During a recent T-SQL Tuesday Boris Hristov (b/t) taught me about SET NOEXEC ON in his post Teaching and Learning. The NOEXEC Thing. When this setting is turned on queries in the current window are compiled but not actually executed. He suggested putting this command at the top of your query window to prevent accidental executions of the entire query. For example when I’m doing some testing I’ll frequently end up having a single query window an assortment of different queries as I research various problems. The number of times I’ve hit Ctrl-E (execute) and executed the whole thing by mistake is astronomical. Because of this I keep trying to remember to put NOEXEC at the top of each query window. And being me I almost always forget. Imagine how helpful it would be to have it show up every time I open a new script.
I always love it when I learn new information in unusual places. Someone today tweeted a link to SQL Sentries top 10 DBA Mistakes: Horror Stories! and since I had some time I decided to listen to it. About half way in Aaron Bertrand (b/t) mentions modifying the new query template in SSMS. The possibility that I could actually manage this and put NOEXEC at the top of each new query excited the heck out of me.
After a few minutes of googling I found this post asking How to set the default query for the SSMS ‘New Query’ button?. Near the bottom is the answer. Modify the sqlfile.sql file. I tried the paths given in the answer and they didn’t work for me so I just did a search on my directory and found it in the following locations.
- C:\Program Files (x86)\Microsoft SQL Server\120\Tools\Binn\ManagementStudio\SqlWorkbenchProjectItems\Sql
- C:\Users\kenne_000\AppData\Roaming\Microsoft\SQL Server Management Studio\12.0\Templates\Sql
- C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\SqlWorkbenchProjectItems\Sql
- C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\SqlWorkbenchProjectItems\Sql
Given that I do most of my work with SSMS 2014 these days I modified the first one. Editing this file requires a tool opened as administrator. So I hit shift-right-click on notepad and selected Run as administrator and then opened the file. Next I typed in my code and saved.
Now I opened a new query in SSMS and
Woo Hoo! Hopefully this will save me some effort in the future.
In case anyone is wondering the commented out SET NOEXEC OFF was just so I didn’t have to type when I need to turn it back off again.
Edit
Kendra Little (t/b) just suggested this as a piece of code to put at the top.
RAISERROR ('Did you mean to run the whole thing?', 20, 1) WITH LOG;
GO
That way it doesn’t just end but returns an intelligent error. As Kendra said, “Less elegant, but less confusing for a student if they do it by accident”.
SQL Studies 
[…] Studio Online Build Using the DAC with SSMS PowerTip: Read NTFS File System Stream with PowerShell Modifying the “New Query” template. SQL SERVER – The Basics of the Execute Process Task – Notes from the Field #084 Getting File […]
Other thing I do is add a RETURN; after each section (even at the top, instead of SET NOEXEC ON) to avoid running too much if I hit f5
Good idea. However it only helps if the query at the top isn’t destructive. It’s going to run if you do hit F5. Still a good idea and a good habit to be in.
If you put RETURN where you put SET NOEXEC ON, *nothing* runs by accident. The only query at the top is the one-line “RETURN;” You have to explicitly highlight the code to run and then hit F5.
I like it! That has the benefit of not having to turn NOEXEC back on again.
FYI I just had it pointed out to me that a RETURN at the top will only stop the code before the first GO statement.
[…] (Management Studio). I’ve used Aaron Bertrand’s (b/t) query window color format and modified my new query template. So while I was at it, I finally decided that since I couldn’t find a short cut to open the […]
[…] other installers. Now it’s time to set things up. Aaron Bertrand’s (b/t) color scheme, change the New Query template and map a keyboard shortcut to change connection. And of course open up my common windows (Object […]
[…] Modify the New Query template I add the statement RETURN at the top of any new query that I open. […]
[…] your window or that you are protected by a RETURN or SET EXECUTION OFF at the top of your screen. I have this put in place by default on new query windows. This protects you from running too much code by […]
You can set the NoExec on in Tools>>Options as well, SSMS 2016, maybe others?
Yea, but if I understand correctly that turns it on for the query. The objective here is to only turn it on when you make a mistake and run the entire query instead of just the piece you want.
In general I like Kendra’s approach best at this point since it actually returns an error that explains what you did. Another option is to put RETURN at the top. It has the same effect but doesn’t make it so you have to change a setting back to run anything.
C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\SqlWorkbenchProjectItems\Sql