Published on

November 13, 2014

Customization of New Query in SQL Server Management Studio

Have you ever wished that certain commands or queries were readily available as soon as you opened SQL Server Management Studio? Maybe you’ve had a scare like my friend, a senior DBA, who had a junior DBA accidentally run a drop table command on a production server. To prevent such mishaps, many DBAs now run the select @@servername command before executing any query on any server. In this blog post, we will explore how to customize the new query window in SQL Server Management Studio to make frequently used commands readily available.

When you launch SQL Server Management Studio, it loads a file called “SQLFile.sql” from the operating system and displays its contents in the new query window. The location of this file depends on the location of SSMS.exe and the version of SQL Server you are using. For example, on my 64-bit machine with SQL Server 2012 and SQL Server 2014, the location of SSMS.exe is “E:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio”. If you are running a 32-bit operating system, the location would be “:\Program Files\Microsoft SQL Server\110\Tools\Binn\ManagementStudio”.

To customize the new query window, you can navigate to the “SqlWorkbenchProjectItems\sql” folder within the SSMS.exe directory. In this folder, you will find the “SQLFile.sql” file, which is the file that is loaded as the new query window. You can modify this file to include the commands or queries that you frequently use. For example, you can add the following commands:

/*
select @@version
go
select @@servername
go
Select *
from sys.dm_exec_requests
where blocking_session_id <> 0
*/

After modifying the file, whenever you open a new query window, it will display the exact text that you have written in the “SQLFile.sql” file. This allows you to have your frequently used commands readily available.

It is important to note that the “SQLFile.sql” file is used by all user profiles on the machine. If you accidentally delete this file, you will encounter an error when attempting to open a new query window. To fix this, you can create an empty file with the same path and name as mentioned in the error message.

I hope you found this tip on customizing the new query window in SQL Server Management Studio useful. By customizing the “SQLFile.sql” file, you can have your frequently used commands readily available as soon as you open SQL Server Management Studio. Let me know if you will be using this customization technique!

Click to rate this post!
[Total: 0 Average: 0]

Let's work together

Send us a message or book free introductory meeting with us using button below.