Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Customize SSMS query window for standard comment block and frequently used commands


By:   |   Last Updated: 2011-05-04   |   Comments (9)   |   Related Tips: More > SQL Server Management Studio

Problem

As a best practice, most companies follow a standard comment format at the beginning of stored procedures and functions. In addition, it is also a good idea to include comments in all scripts you write even if they are just save as .sql or .txt files. Manually we often add a standard comment block to the header of our scripts using copy and paste, but in this tip we look at how to automate adding a standard comment block for every set of code we write using SSMS.

Solution

As a best practice you should create a standard comment format at the beginning of your T-SQL codes such as the below example. If you are SQL Developer and your job is to only write stored procedures and functions, you can automate adding a standard comment header when you open a new Query window.

--Sample Comment format
/*
*************************************************************************************
 [OBJECT NAME]: Stored Procedure Name
 [DESCRIPTION]: SP Description
 Copyright by Company Name
 [NOTES]: 
 
 [PARAMETER BREAKDOWN]:
  @parameter name - Parameter description
  @parameter name - Parameter description
 [SAMPLE CALL]:
 
 exec spName @parameter1 = 'MSSQLTips', 
 @parameter2 = 'Jugal', 
 
 [SQL Version Support]: SQL Server 2000, 2005, 2008
 
 [MODIFICATION HISTORY]:
 Date        Author                  Comment
 ----------- ----------------------  -----------------------------------------
 6/8/2009 Jugal Shah    Inception
 6/23/2009 Jugal Shah   add dbmail feature
*************************************************************************************
*/

If you don't want a standard comment block another option it so include frequently used T-SQL statements such as the below commands.

--Sample T-SQL Code
select @@servername
select @@version
select * from sys.sysprocesses where blocked <> 0
sp_who2 active
sp_heldb
xp_fixeddrives

So if I use the second example, when I open a new query window in SSMS I get these commands every time as shown below.

create a standard comment format at the beginning of your t-sql codes

To automate the comment/T-SQL in all new query windows, you have to modify the SQLFile.sql file which is located in:

  • On 32-bit machine "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\SqlWorkbenchProjectItems\Sql" folder.
  • On 64-bit machine "C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\SqlWorkbenchProjectItems\Sql" folder.

You could also do a search for SQLFile.sql as well. The below image shows the file.

you have to modify the sqlfile.sql file to automate the comment

Once you find the file, you can edit the SQLFile.sql with any text editor (i.e. notepad) and add your own standard comment format or T-SQL code and save the file. Then whenever you open a new Query window you will see the comments/T-SQL that you entered into the SQLFile.sql file.

Note that you must have the proper permission to edit SQLFile.sql. I used Notepad and used the "Run as administrator" option to edit and save the file. This just depends on the security that was setup on your desktop. Also, since this file is saved locally each person that wants this has to do this on thier machine.

Next Steps


Last Updated: 2011-05-04


next webcast button


next tip button



About the author
MSSQLTips author Jugal Shah Jugal Shah has 8+ years of extensive SQL Server experience and has worked on SQL Server 2000, 2005, 2008 and 2008 R2.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Wednesday, April 19, 2017 - 11:54:28 AM - Joshua Guttman Back To Top

 SQL Server 2016 update:

 C:\Program Files (x86)\Microsoft SQL Server\130\Tools\Binn\ManagementStudio\SqlWorkbenchProjectItems\Sql\SQLFile.sql

 

 


Wednesday, May 11, 2011 - 9:29:32 AM - Samson J. Loo Back To Top

This works out great. I am running Windows 7 x64 Home Premium with SQL Server 2008 R2 Developer x64 and I found my file located at: C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\SqlWorkbenchProjectItems\Sql.


Wednesday, May 04, 2011 - 1:00:44 PM - dj Back To Top

Of course.


Wednesday, May 04, 2011 - 12:49:35 PM - Jugal Shah Back To Top

Have you saved the SQLFile.SQL after editing?


Wednesday, May 04, 2011 - 12:30:40 PM - dj Back To Top

Finding the file was no trouble.  Editing and saving were no trouble either. I closed and opened SSMS (2008 R2) but still get blank new queries.

 


Wednesday, May 04, 2011 - 12:07:18 PM - Jugal Shah Back To Top

Seach for the SQLFile.SQL using Operating System search functionality. Let me know if you will get the file.


Wednesday, May 04, 2011 - 11:55:46 AM - dj Back To Top

Doesn't work for me on XP (x86) or Win7 (x64).  :(


Wednesday, May 04, 2011 - 10:03:05 AM - Greg Robidoux Back To Top

Hi Bob,

thanks for the great tip.  If you are interested in writing a tip about this, please let us know.

http://www.mssqltips.com/contribute.asp


Wednesday, May 04, 2011 - 9:41:57 AM - Bob Back To Top

I really appreciate these tips in my RSS feed each day! Based on the code in your comment area, I have one tiny addition that I've found tremendously helpful over the years (learned by watching someone else code, of course). Often people leave commas at the end of a line, like in your comment section example:

 

 exec spName @parameter1 = 'MSSQLTips', 
 @parameter2 = 'Jugal'
However, if you place commas at the beginning of the NEXT line, you can comment out and/or highlight just the lines you want to use and even provide alternatives embedded in the code that you can active/de-activate, like in these examples:

 

 

 exec spName 
   @parameter1 = 'MSSQLTips'
,  @parameter2 = 'Jugal'
, @parameter3 = 'do not highlight this line when running the code and you get the default instead' 

SELECT TOP 100 * 
FROM Orders o
-- INNER JOIN OrderItems oi ON o.orderID = oi.orderID
INNER JOIN Customers c ON o.customerID = c.customerID

SELECT
  o.orderID
--, o.orderDate
, o.orderTotal
, c.companyName
, c.phone
FROM Orders o
INNER JOIN Customers c ON o.customerID = c.customerID
-- WHERE o.orderTotal > 1000
ORDER BY o.orderTotal DESC, c.companyName
-- ORDER BY o.orderID

It's a new discipline to learn for some, but it can save some time and make trying out alternatives easier.

 


Learn more about SQL Server tools