Sql Server Management Studio: IronPython Extensible add-in

I wanted to write an add-in that helps me with the complicated tasks in SSMS like navigating large procedures or inserting some text like comments.

You can find the download details at the bottom of this document, but first some more info.

The SSMS add-in environment can be a daunting task to figure out so I decided to use my favourite scripting language IronPython to create a framework that I can explore the SSMS environment.



I created a control Sql Navigator:
 

To refresh the navigator press: CTRL+SHIFT+F5
(Can be changed in the script file, can also be hooked up to automatic changing of the active window)

The navigator will parse all query windows and extract the different objects and line locations so you can quickly jump to any of the objects in the document.

Take the following code:

   1: CREATE TABLE #TempTable1(

   2:     Id INT NOT NULL

   3:  

   4: )

   5: CREATE UNIQUE CLUSTERED INDEX IX_Clustered ON #TempTable1

   6:  

   7: -- #region Region1

   8: -- some block of code here

   9: -- #endregion

  10:  

  11: DECLARE @Variable1 INT = 0

This is represented above in the navigator.

Want to add more support for different parts of the query window, edit the file:

c:\Program Files (x86)\ACSR\IronPythonAddIn\Scripts\Expressions.txt
Add the name and expressions:

SelectStatements,SELECT.*?FROM[\s]+[\[]?([#\w\.]+)[\]]?
InsertStatements,INSERT[\s]+INTO[#\s]+([\w\[\]\.]+)

And the SQL:

  SELECT * FROM SomeTable
  INSERT INTO SomeTable ....

I also created additional supportive windows:

A Logger Window.

But what is the most useful is the Interactive Window

But lets skip over it for a moment.

All scripts are in the scripts folder:



To create a new command, create a new script in this directory:

SSMSSampleCommany.py


Locate the Script SSMSAddin.py

Import your script:

   1: try:

   2:     from SSMSSampleCommand import *

   3:     from SSMSCore import *

Find and create your command:

   1: class DefaultAddIn(SqlAddIn):

   2:     def InternalHookEvents(self, Control):

   3:         

   4:         commandBar = self.CreateCommandBar("IronPython")

   5:         self._sqlNavigator = SqlNavigator(self, commandBar)

   6:         FormattingCommands(self, commandBar)

   7:         sampleCommand = SampleCommand(self, commandBar,  "IronPythonSampleCommand", "Sample Command", "Sample Command ToolTip", 13)       

   8:         sampleCommand.GetCommandInstance().Bindings = "Global::ctrl+Shift+F12";   

Code your command:

   1: from SSMSCore import *

   2: from SSMSUtilityFunctions import *

   3: from System.Windows.Forms import *

   4:  

   5: class SampleCommand(CommandHandler):

   6:     def InternalExecute(self, ExecuteOption, VariantIn, VariantOut, Handled):

   7:         print "MyCommand Executed"

   8:         MessageBox.Show("My Command Executed") 

   9:         self._DTE.ExecuteCommand("File.NewQuery") # opens a new query

  10:         self._DTE.ActiveWindow.Selection.Insert("SELECT GETDATE()")

  11:         self._DTE.ExecuteCommand("Query.Execute")

The sample prints that the command was executed



Shows a dialog



The opens a new query




And Executes it.


Also the Add procedure comment command:





The first time:




Outputs the comments:

Now make sure the comment is selected and run it again:




Regular expressions parses the document and picks up the old history.

Add your comment:




Maintains history by appending each comment.


The initials are hard coded, please see the file:


SSMSFormattingCommands.py


If you’d like to change it.

Exploring the environment:

This is one of the exciting parts where you can explore the IDE for objects and method and write test code for your commands or add ins. Or if you just want to manipulate the windows and text.

Here we can explore the SQL Server DTE object get all the methods members and call them in an explorative way.





This is enough for now, in a later post I will describe how I built it.

The installation package including all source code can be downloaded from GIT Hub:


https://github.com/TheWalkingDev/Projects/tree/master/SQL%20Server/Add-Ins

 Click on download, and find the package file: