1

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 
2

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

|improve this answer|||||
  • It is working, There is <Shortcut></Shortcut> node. Is it possible to assign any shortcut to this code snippet? For every time pressing the Ctrl + K, S and select this snippet is, again repeating of the work – Arulkumar Aug 18 '16 at 10:41
0

I'm not aware of any way to natively do that in SSMS, but this free plugin tool allows that option and so much more

http://www.ssmsboost.com/

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.