Thursday, May 26, 2011

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

You should create a standard comment format at the beginning of your T-SQL codes such as the below example.  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
  @parameter name - Parameter description
  @parameter name - Parameter description
 exec spName @parameter1 = 'MSSQLTips', 
 @parameter2 = 'Jugal', 
 [SQL Version Support]: SQL Server 2000, 2005, 2008
 Date               Author            Comment
 ------------------ -----------------  -----------------------------------------
 26 May 2011 Arun Ladha    Inception
 26 May 2011 Arun Ladha   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

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.

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.

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.


No comments:

Post a Comment