It is normal to keep writing very similar SQL statements while working on database logic. Over the course of the past 10 years, I’ve been using SQL Prompt from RedGate in order to make my life easier while writing repeated SQL statement, it is very nice tool that is integrated with SQL Server Management Studio (SSMS). I mean by repeated statement things like “Select * from “, “Select Count(1) from “, etc… to a more complex statement which is checking what is being running currently on specific database. well, I can save these complex statement into files and just keep opening these files and use them. But that’s takes time. RedGate SQL Prompt, is relatively expensive tool specially when you need to purchase it to for your large team.
There is a cool feature on SSMS that helps me solving this issue. which is basically code snippet. which I save certain queries on SSMS IDE and use it later on. in order to do that I follow the following steps:
1. Open Notepad or any text editor, paste the below XML
<?xml version="1.0" encoding="utf-16"?>
<CodeSnippets>
<CodeSnippet Format="1.0.0">
<Header>
<Title>createIndex</Title>
<Shortcut>createIndex</Shortcut>
<Description>Create Index</Description>
<Author>Yasser Jaber</Author>
<SnippetTypes>
<SnippetType>Expansion</SnippetType>
</SnippetTypes>
</Header>
<Snippet>
<Declarations />
<Code Language="sql"><![CDATA[
You SQL Snippet Goes here
]]></Code>
</Snippet>
</CodeSnippet>
</CodeSnippets>2. Before saving the file, change the text “You SQL Snippet Goes here” to your repeating sql statement.
3. Save the notepad with extension .snippet
4. On SSMS, select Tools menu , then ‘Code Snippets Manager‘, this will bring below popup
5. Click on Import, navigate to where you saved the notepad file, and select it. choose the folder that you want to have the snippet to show
By doing above steps, you will have new snippets imported into your SSMS IDE. to use you, do right click, click on “Insert Snippet”, select your correct folder, then select your correct Snippets.