SQL SERVER – Customization of New Query in SQL Server Management Studio

SQL
4 Comments

SQL SERVER - Customization of New Query in SQL Server Management Studio helpbowl Recently I was working with one of my friends who handles production server databases as a senior DBA. He told that recently one of the Jr. DBA was fired from his company because he has run a drop table command on a production server. He was scared and from that time onwards, every DBA has been asked to run select @@servername commands before executing any query on any server. Similar to this situation, many times a DBA wants a set of command, which they use very frequently, should be available as soon as SQL Server Management Studio is opened. Let us learn about Customization of New Query in SQL Server Management Studio.

Both of the above situation can be handled by the trick explained in this blog.

Whenever we launch SQL Server Management Studio, it loads “SQLFile.sql” from the operating system and shows the text in the New Query Window. The location of this file depends on location of SSMS.exe and version of SQL Server. My machine has SQL Server 2012 and SQL Server 2014.

Here is the location of SSMS.exe on my 64 bit machine for SQL 2012

E:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio

SQL SERVER - Customization of New Query in SQL Server Management Studio ssms-nq-01

Solarwinds

If you are running 32 bit operating system then it would be

<Drive>:\Program Files\Microsoft SQL Server\110\Tools\Binn\ManagementStudio

Here is the mapping of SQL versions and internal version numbers (which is 110 from SQL 2012 in the above example).

Microsoft SQL Server 2014120
Microsoft SQL Server 2012110
Microsoft SQL Server 2008 R2100
Microsoft SQL Server 2008100

Once you find SSMS.exe, then we can navigate to SqlWorkbenchProjectItems\sql folder as shown below.

SQL SERVER - Customization of New Query in SQL Server Management Studio ssms-nq-02

The file highlighted “SQLFile.sql” is the file which is loaded as a new query window.

On my machine, I have modified the file and written below

/*
select @@version
go
select @@servername
go
Select *
from sys.dm_exec_requests
where blocking_session_id &lt;&gt; 0
*/

After modifying, whenever you open new windows, it would be as below.

SQL SERVER - Customization of New Query in SQL Server Management Studio ssms-nq-03

As we can see that this is EXACTLY same text which we have written in the SQLFile.sql file.

Be Aware: This file is used by all user profiles on the machine. If you delete this file by mistake, you would get below error whenever a new query window is attempted.

SQL SERVER - Customization of New Query in SQL Server Management Studio ssms-nq-04

—————————
Microsoft SQL Server Management Studio
—————————
Cannot find template file for the new query (‘E:\Program Files (x86)\Microsoft SQL Server\120\Tools\Binn\ManagementStudio\SqlWorkbenchProjectItems\SQL\SQLFile.sql’).
—————————
OK
—————————

To fix this, you can create an empty file with the same path and name in the location as per error message. The path of the error message would vary based on installation on your machine. Hope you found this tip on SSMS useful and do let me know if you will be using the same.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
, ,
Previous Post
SQL SERVER – Identifying T-SQL Missing Parameter Data Types with SQL Server
Next Post
SQL SERVER – Inside Temp table Object Creation – Part II

Related Posts

4 Comments. Leave new

  • This is a nice tip, thanks.

    In regards to the actual problem here (accidentally dropping the wrong table) – wouldn’t it be simpler to create a trigger to avoid a DROP statement being executed? After all, if the @@servername is being run for a person to check, then the person can still easily get this mixed up. Secondly if the @@servername results are being used to drive some logic in the query window – it’s still possible for the logic to be accidentally modified when the DBA is changing the code.

    Reply
  • was really helpful.

    Reply
  • Sanjay Monpara
    December 3, 2014 8:13 pm

    wow!, always something new in your tips, thanks…

    path in my pc is
    C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\SqlWorkbenchProjectItems\Sql

    Reply
  • It doesn’t work when you right click on a DB and choose New Query….

    Reply

Leave a Reply

Menu