Copy-Rename SQL Prompt Snippets to SQL Server Snippets

Return to Resume   GoTo Code
  1. SQL Prompt has a nice interface for developing TSQL Snippets

  2. But what if you don't have SQL Prompt installed and you have a lot of ".sqlpromptsnippet" files that you want to leverage

  3. They are not automatically available in SSMS Code Snippet Manager

  4. The SQL Snippet folder "My Code Snippets" is empty

  5. Use PowerShell to Copy all ".sqlpromptsnippet" files from the SQL Prompt folder to the "My Code Snippets" folder and Rename the extension of the copied files from ".sqlpromptsnippet" to ".snippet"

  6. Verify the files were copied and renamed>

  7. Now simply use SSMS to import the copied snippets

  8. The SQL Snippet folder "My Code Snippets" ... now has usable snippets


# Set the variables
$sourceDir = "C:\Users\pc\OneDrive\SQLScripts\Snippets\SQLPromptSnippets\"
$sourceFormat = ".sqlpromptsnippet"
$outDir = "C:\Users\pc\OneDrive\SQLScripts\Snippets\SQL\My Code Snippets\"
$outName = "Mod02_"
$outFormat = ".snippet"

# Get shell object
$Shell = new-object -com Shell.Application

# Validate
$folder = $shell.namespace($sourceDir)
if (!$folder)
 "Folder: {0} does not exist" -f $sourceDir  

# Get folder and item details into the variables
# Output the folders details
if ($folder)
 # Number of "$sourceFormat" Files in Folder
 $Dir = get-childitem $sourceDir
 # Get-Variable dir;

 #$sfFiles = $Dir | where {$_.extension -eq "$sourceFormat"}  #this way also works
 $sfFiles = $Dir | where-object {$_.Name -like "*$sourceFormat"}
 # Get-Variable sfFiles;

 $sfCount = $sfFiles.count
 $sfFiles | format-table name
 "Folder `"{0}`" contains  `"{1}`"  `"{2}`"  files" -f $sourceDir, $sfCount, $sourceFormat

# Copy & rename the $sourceFormat files into the destination directory
$sfFiles | Copy-Item -dest {"$outDir" + $_.BaseName + ($i++) + "$outFormat"} -Whatif
$sfFiles | Copy-Item -dest {"$outDir" + $_.BaseName + ($i++) + "$outFormat"} -Confirm

# Wipe out variables
Remove-Variable -Name Dir
Remove-Variable -Name sourceDir
Remove-Variable -Name sourceFormat
Remove-Variable -Name outDir
Remove-Variable -Name outFormat
Remove-Variable -Name sfFiles
Remove-Variable -Name i