ApexSQL Complete is a FREE Visual Studio and SQL Server Management Studio add-in which offers auto-complete features that predict objects, keywords, schemas, and users.
In the previous articles ApexSQL Complete vs. SSMS – Part 1 – Object recognition and ApexSQL Complete vs. SSMS – Part 2 – Auto-complete DDL and DML statements, we have shown ApexSQL Complete’s recognition of typed terms and support for auto-complete DDL and DML statements. In this sequel, we will compare ApexSQL Complete’s and SQL Server Management Studio’s snippet features.
Using T-SQL code snippets reduces the time and the number of lines in the code that has to be typed. By utilizing snippets, T-SQL script can be created without the need of remembering the commands and their syntax. In the following text, we will analyze and compare the differences in snippets usage in ApexSQL Complete and SQL Server Management Studio.
Creating and using custom code snippets in SSMS
In SQL Server Management Studio, snippets are based on templates which are XML files. To create a custom snippet you have to perform the following operations:
- Create a snippet using XML syntax and save it with the .snippet extension. Note: When creating snippets in SSMS, the snippet type (Surrounds With or Expansion) has to be specified.
Below is an example of the SSMS XML based snippet:
<?xml version=“1.0“encoding=”utf-8“?>
<CodeSnippets xmlns=“http://schemas.microsoft.com/VisualStudio/2005/CodeSnippet“>
<CodeSnippet Format=“1.0.0“>
<Header>
<Title>SeeObjectCode</Title>
<Description> View full code of the selected object </Description>
<Author></Author>
<SnippetTypes>
<SnippetTypes>SurroundsWith</SnippetTypes>
</SnippetTypes>
</Header>
<Snippet>
<Declarations>
<Literal>
<SnippetTypes>
<ID>View_Object </ID>
<ToolTip> </ToolTip>
<Default> </Default>
</Literal>
</Declaration>
<Code Language=“SQL“>
<![CDATA[
EXEC sp_helptext ‘$selected$’;
]]>
</Code>
</Snippet>
</CodeSnippet>
</CodeSnippets>
Register the created code snippet in SSMS:
- Under the Tools menu, choose the Code Snippets Manager command
- Click the Add button and navigate to the folder where the snippet is saved
- Add the folder
The next step is importing the T-SQL code snippet:
- Under the Tools menu, choose the Code Snippets Manager command
- Click the Import button and select the snippet file from the folder
- Import the snippet
To use the created snippet highlight the name of the object in the query tab:
Right-click and choose the Surround With command from the context menu. Here, it is important to notice that the Insert Snippet command cannot be used for snippets with embedded variables and vice versa:
Choose the folder where the snippet is previously saved and select the snippet:
The variable $selected$ from the above XML example will be replaced with the name of the object highlighted in the query editor
Creating and using custom code snippets in ApexSQL Complete
In ApexSQL Complete, creating a new snippet or editing an existing one is a bit easier. There is no need to write long XML code:
EXEC sp_helptext ‘$SELECTED$’;
To create a new code snippet, choose the Options dialog from the ApexSQL Complete menu and select the Snippets tab:
Click the Add button to open the Create new snippet dialog and type the T-SQL code:
Both Create new snippet and Edit a snippet dialogs contain a list of variables which can additionally streamline coding. For example using $DATE$ variable in the snippet code will return the current date in the query editor, $SERVER$ variable will return the connection server name, $LOGIN$ variable will show a connection login, $USER$ variable will return a connection user name, and so on.
To use the snippet created in this example, highlight the object in the query window:
Right-click and choose the Insert Snippet command. Unlike in SSMS, ApexSQL Complete has a single command for inserting snippets since it is capable of recognizing and replacing embedded variables unattended:
Select the snippet from the drop-down list:
Choosing the snippet, View_Object in this example, will replace the $SELECTED$ variable in the snippet with the name of the highlighted object from the query
The resulting SQL query is the same as the resulting query from the SSMS example
See also:
ApexSQL Complete vs. SSMS – Part 1 – Object recognition
ApexSQL Complete vs. SSMS – Part 2 – Auto-complete DDL and DML statements
ApexSQL Complete vs. SSMS – Part 4 – SSMS Intellisense add-in and native SSMS Intellisense
ApexSQL Complete vs. SSMS – Part 5 – Options not supported by SSMS intellisense
October 1, 2013
