Sql-server – SSMS shortcut to comment/uncomment a specific part of the query

keyboard shortcutssql serverssms

I'm looking for an option to comment/uncomment a particular part of the query with the Slash Star Comment option.

Scenario:

While debugging the queries with N numbers of JOINs, I comment out each table in the join and its related conditions one by one and find which table causing the issue or wrong data. If the commented table didn't cause the issue, I will uncomment the table and its related condition and try with the next table.

For this purpose currently I manually add — comment in front of the particular line or press Ctrl + C to comment the entire line, if the select/conditions are in middle of the line, I move the select/condition alone to the next line and add — comment. If there is no issues with the join, again I undo the changes or Ctrl + U to uncomment.

Moving the condition to the new line and adding the comment is burden to me, some time it caused bad alignment too.

Expectation:

What I want is need some shortcut key to add the /* in front and */ in end of the selected text.

Samples:

The below is the sample case, which my query looks like (in actual it may contain more select columns, tables and conditions).

SELECT T1.Id, T1.Name, T1.Designation, T1.CreatedDate, 
       T2.AccountName, T3.LinkId, T3.LinkName, T4.OwnershipName
FROM TableOne T1
INNER JOIN TableTwo T2 ON T2.Id = T1.Id AND T2.Name = T1.Name
INNER JOIN TableThree T3 ON T3.Id = T2.Id AND T3.IsActive = 1
INNER JOIN TableFour T4 ON T4.Id = T1.Id AND T4.LinkId = T3.LinkId AND T4.IsActive = 1
WHERE T1.CreatedDate BETWEEN @FromDate AND @ToDate AND T1.DeletedDate IS NULL
    AND T2.DeletedDate IS NULL AND T3.DeletedDate IS NULL AND T4.DeletedDate IS NULL

Here if I want to comment the TableThree and its related content in the SELECT, JOIN and conditions in WHERE. I will select the content and press the shortcut key to add the /* */ comment separately.

Expected the behavior is:

SELECT T1.Id, T1.Name, T1.Designation, T1.CreatedDate, 
       T2.AccountName/*, T3.LinkId, T3.LinkName*/, T4.OwnershipName
FROM TableOne T1
INNER JOIN TableTwo T2 ON T2.Id = T1.Id AND T2.Name = T1.Name
/*INNER JOIN TableThree T3 ON T3.Id = T2.Id AND T3.IsActive = 1*/
INNER JOIN TableFour T4 ON T4.Id = T1.Id /*AND T4.LinkId = T3.LinkId*/ AND T4.IsActive = 1
WHERE T1.CreatedDate BETWEEN @FromDate AND @ToDate AND T1.DeletedDate IS NULL
    AND T2.DeletedDate IS NULL /*AND T3.DeletedDate IS NULL*/ AND T4.DeletedDate IS NULL

Best Answer

You can easily do this with Code Snippets, something like this will work for your example:

<?xml version="1.0" encoding="utf-8" ?>
<CodeSnippets  xmlns="http://schemas.microsoft.com/VisualStudio/2005/CodeSnippet">
    <_locDefinition xmlns="urn:locstudio">
        <_locDefault _loc="locNone" />
        <_locTag _loc="locData">Title</_locTag>
        <_locTag _loc="locData">Description</_locTag>
        <_locTag _loc="locData">Author</_locTag>
        <_locTag _loc="locData">ToolTip</_locTag>
        <_locTag _loc="locData">Default</_locTag>
    </_locDefinition>
    <CodeSnippet Format="1.0.0">
        <Header>
            <Title>SLASH STAR COMMENT (surrounds)</Title>
            <Shortcut></Shortcut>
            <Description>Surround selected text with slash star comment block /**/</Description>
            <Author>wBob</Author>
            <SnippetTypes>
                <SnippetType>SurroundsWith</SnippetType>
            </SnippetTypes>
        </Header>
        <Snippet>
            <Declarations>
                <Literal>
                    <!--<ID>x</ID><ToolTip>y</ToolTip><Default>z</Default>-->
                </Literal>
            </Declarations>
            <Code Language="SQL">
                <![CDATA[/* $selected$ $end$ */]]>
            </Code>
        </Snippet>
    </CodeSnippet>
</CodeSnippets>

Save the file with a .snippet extension and follow the instructions from here to add the snippet to SQL Server Management Studio (SSMS).

Instructions for use

  1. highlight the text you want to surround with the comment block in the query editor
  2. Press Ctrl+K, Ctrl+S to summon the 'Surround With' snippets. Note the comma between the two sequences, it means "press Ctrl+K, then press Ctrl+S in quick succession"
  3. Select your snippet from the list
  4. Your highlighted text is surrounded with the comment block
  5. press Ctrl+Z to Undo - this is the normal windows undo command. It will remove the comment block as long as you haven't made any other edits.

Code snippets in action

Related Question