Migrate SQLPrompt Snippets to VSCode

-
 I love snippets; love em. And I have a whole bunch in RedGate SQL Prompt. Now I want to be able to use those in VSCode as well, but boy do I dread having to retype all of them. Solution? Python! First arg is the path where your SQLPrompt snippets are Second arg is the directory where you want it to spit out a "sql.json" file with all your snippets. """ A script to translate sqlprompt snippet files to vscode formatted snippets """ import os import json import glob import io import argparse class SQLPromptPlaceholder :     """Represents the values of a SQLPrompt placeholder"""     def __init__ ( self , name , default_value ):         self . name = name         self . default_value = default_value class SQLPromptSnippet :     """Represents the content of a SQLPrompt snippet"""     @ staticmethod     def from_file ( filename ):         """Generates an instance fr...
Post a Comment

SqlXP

-

I think SQL Extended Properties are awesome. In a nutshell, they allow you to attach arbitrary key-value pairs to SQL objects of almost any kind. I've used them for things like simple documentation, to enabling special functionality for maintenance procs, or marking all tables serving a particular function in a system. They're just cool. The biggest pain in the butt IMHO is just managing them; creating them, updating them, and to a lesser extent, viewing them.

The biggest problem is scripting them. SQL gives three procs for managing extended properties:

Each of those does exactly what it says on the tin. The pain is that they will fail if you start in the wrong state. I.e. 

  • Add will fail if the property already exists
  • Update will fail if the property does not already exist
  • Drop will fail if the property does not already exist
Which means if you want to add XP management to any re-runnable script, you have to bake in if-then-else logic for every call.

I created a github repo for a project I created called SqlXP which adds some utilities to make this process less painful. It centers around a new marked system object called sp_setextendedproperty which basically allows you to use whichever of the official procs is appropriate based on the inputs. You can tell it do drop stuff, add if not exists, or upsert (add if not exists, and update if different).

The rest of the objects are basically quality of life improvements. For example, 99% of the time when I'm adding an XP, it's to a schema object (db.schema.myObject). Which makes a bunch of the parameters to the official procs repetitive (e.g. level0type = 'SCHEMA', level0name = 'dbo', level1type = 'Table'...etc).

I've made helper procs which can just figure out the types for most of the common object types, including going down to the "minor type" level (i.e. parameter or column). 

Anyway, check it out, and let me know what you think.

Comments

Post a Comment

Popular posts from this blog

Migrate SQLPrompt Snippets to VSCode

-
 I love snippets; love em. And I have a whole bunch in RedGate SQL Prompt. Now I want to be able to use those in VSCode as well, but boy do I dread having to retype all of them. Solution? Python! First arg is the path where your SQLPrompt snippets are Second arg is the directory where you want it to spit out a "sql.json" file with all your snippets. """ A script to translate sqlprompt snippet files to vscode formatted snippets """ import os import json import glob import io import argparse class SQLPromptPlaceholder :     """Represents the values of a SQLPrompt placeholder"""     def __init__ ( self , name , default_value ):         self . name = name         self . default_value = default_value class SQLPromptSnippet :     """Represents the content of a SQLPrompt snippet"""     @ staticmethod     def from_file ( filename ):         """Generates an instance fr...
Read more

Create CSVs from SQL Server

-
 So you want to automate export some data from SQL to CSV files?  For one-off things, there are a couple approaches you can use SSMS for, however there are some major drawbacks of doing things there, most notably, it's all manual; you can't script any of this stuff out. Still, I'll discuss a few methods in SSMS before the cool stuff which is with BCP or Powershell SSMS These methods work fine for one-time exports that you don't need to automate and you don't need a high level of control of the output. As a Result Set One simple way is to set the output to either text (CTRL-T) or to file (CTRL-F). If you set it to text, copy/paste the results into a text file, or if you're using results to file, just name the file anything .csv  (instead of .rpt) . Make sure to clean up any header (like column names, and the big row of hyphens)  and footer (like the rowcount, if you didn't set nocount on). You can adjust some of the settings those methods use to generate the ...
Read more

Powershell Script: to Create Git Repo with .gitignore

-
 I'm a scripting junkie, so I got tired of having to download or copy/paste a gitignore file into my git repos all the time. To that end, I created the script below. The involved function is gitig which downloads a boilerplate .gitignore file from github (i have it defaulting to downloading the VisualStudio .gitignore, but you can change that, or provide your own at call time). The other function is gitinit which calls gitig followed by git init . That way you can initialize a new git repo with a boiler plate .gitignore script with a single powershell command. To install these,  Open Powershell Type "notepad $profile" This will open notepad with your powershell profile Paste the code below at the bottom of that file Save and close the profile file Restart powershell Now you can type gitinit from powershell to create a repository with a boilerplate .gitignore anywhere you want. function gitig( [String]$Name = "VisualStudio", [switch]$Overwrite = $...
Read more