Share t-sql snippets to the team with SQL Prompt

Red-Gate SQL Prompt is a plugin for SQL Server Management Studio that makes the developers life easier when writing t-sql. It provides improved intellisense, indent and coding styles, and also a snippet manager feature that allows the developer to create a set of useful shortcuts. Those autocomplete items can be really useful for a team, let’s see how we can share them in a team.

Here is an example (CREATE TABLE snippet):

sni

For further information about the product, click here.

After the plugin setup you will find a set of built-in snippet located, by default, on %USERPROFILE%\AppData\Local\Red Gate\SQL Prompt 5\Snippets (this is for version 5). Every snippet is a file with the .sqlpromptsnippet extension.

From a team perspective it could be important to share those files between developers. Suppose to have some database dedicated snippets, for example templates on roles, users, index options and so on. In those cases we can create snippets as described below:

sni2

That DEMO snippet will be added onto the default list.

How can we share the new item to the team? Following the steps I made:

  • dropbox installed
  • folder content copied into dropbox
  • SQL Prompt default folder changed

I made the copies and changes with powershell.

1) dropbox folder

After dropbox is installed, create a folder that will contain snippets and that will be shared. The name in this example will be “Snippets”. Share the folder with other developers.

2) powershell script

After the “Snippet” folder is created and shared, execute the following powershell script on the pc you want to configure:
#specify the sql prompt folder (registry)
$SQLpromptFolder = "SQL Prompt 5"

#copies the snippet files into the dropboxfolder
$localAppDataSnippets = $env:LOCALAPPDATA + "\Red Gate\" + $SQLpromptFolder + "\Snippets\*.sqlpromptsnippet"
$newSnippetFolder = $env:USERPROFILE + "\Dropbox\Snippets"
Write-Host "Copying *.sqlpromptsnippet files from " + $localAppDataSnippets + " to " + $newSnippetFolder + ".."
Copy-Item $localAppDataSnippets $newSnippetFolder

#registry property
$SQLpromptRegistryFolder = "HKCU:\Software\Red Gate\" + $SQLpromptFolder
Set-Location $SQLpromptRegistryFolder

Set-ItemProperty . "Snippets Folder" $newSnippetFolder
Write-Host "Restart SQL Server Management Studio to apply.." 
 The script does:
– copy of the folder content into the new dropbox folder
– queries the registry and adds the new folder string for SQL Prompt (documentation here)
The SQL Prompt installed on the pc in which the script is executed will point to the new path configured. When a developer adds a new snippet, this will be shared in real time via dropbox.
Note: this script is valid for the version indicated on $SQLpromptFolder. You can change the value of that variable, but I strongly suggest to read the documentation of the release you want to manage.
Tip: you can use also another storage, let’s say, in the cloud, like skydrive. The powershell script can be shared using the same behavior.
Stay Tuned! 🙂

2 thoughts on “Share t-sql snippets to the team with SQL Prompt

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s