Download Addin Project
Download SQL Script\Data
Introduction
Like most DBA's I find myself repeating the same SQL statements over-and-over again. There has to be an easier way to speed up your SQL productivity. So, instead of typing that "SELECT COUNT(*) FROM XYZ_Table", I decided to develop my own SSMS (12) custom addin, using Visual Studio 13.
Background
Wouldn't it be so much easier to right click a table and get the row count or select a column and find the duplicate values in that table, or run generic common SQL statements against the Master database, all from mouse clicks instead of typing, cutting\pasting or loading a saved SQL query from file and executing it.
Prerequisites
What We Are Looking to Achieve
Below is an example of the custom table context menu that the addin will create (Custom SQL & Script Full Schema). You will also see logic that determines if a user has right clicked on a table or a column and display the appropriate context menu for that object. Below is the context menu for a table. For the column context menu, you are able to find duplicate values in that column.
Below you can see that I have added a new context menu option for columns only. In this example, I find all duplicate values within a certain column. This example will be explained later with another screenshot and the explanation of the SQL\code behind it.
We will also create menu commands to execute global SQL statements. For example below, we are using the "Tools" menu to execute a "Kill Transactions" SQL statement. I have designed the SQL in such a way, it doesn't fully execute the SQL, but it has some lines commented out. Which you can uncomment and run when you are comfortable with the SQL.
Create Your Addin Project
There are a few screens to go through before you get a skeleton Addin project. Below you can see that you will mostly default your way through. Just make sure to install the Visual Studio 13 SDK, as this contains the new Addin project template used in Visual Studio 13.
Project Settings and Deployment Gotchas
Breaking Changes Between SSMS Versions
In the past, when you had to develop an Addin for SSMS 2005 and then develop the same Addin code for SSMS 20088\R2, your code would not compile, as a number of properties\methods were deprecated and not backward compatible. With SSMS 12+, Microsoft have re-engineered (albeit using Visual Studio 10) the objects behind the Addin. Thus, making it more compatible going forward...I would take this with a pinch of salt, but the conflicts should be dramatically reduced.
Deployment Folders
When you want to test your addin within SQL Server, you will need to let SQL Server know that it has to attach the addin when it starts. To do this, simply copy your .Addin file into one of the following paths. Option 2 below, will attach the addin for all users of SQL Server. If the folder(s) below don't exist, then you just need to create the folder structure.
- C:\Users\UserName\AppData\Roaming\Microsoft\MSEnvShared\Addins
- C:\ProgramData\Microsoft\MicrosoftSQL Server Management Studio\11.0\Addins
Assembly References
The area I found most problematic, was the assemblies. As there are similar named and conflicting versions. But the list below should cover most of your Addin tasks. You will be able to see in the project the paths to the assembly (properties of assembly) and use that as a guide to finding the assembly within your environment.
Project Properties
Right click your project and select properties from the context menu. Set the external application that will startup your addin, to the path of your SQL Server Management Studio executable. Something along the line of:
C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\Ssms.exe
Remove the "Command line arguments" and "Working directory" entries.
Configure Addin File XML
Before you can deploy or test your Addin, you will need to configure it. Basically, there are only three elements you need to update.
- Name - change this to Microsoft SQL Server Management Studio
- Version - insert an asterisk, as this will cater for various versions of SQL Server (as there will be CTP versions etc.)
- Assembly - Enter the path to your addin assembly (debug when testing and release when you are ready to deploy)
Turn Off PInvoke Exception When Debugging
When debugging, the PInvoke error will popup, this is not an issue at runtime, its because of the Long and Int conversions between C++ and C# are causing a conflict (debug only). So, to stop the popups, simply turn the PInvoke of within Visual Studio.
Click on Debug.Exceptions, and scroll down to the Pinvoke Stack Imbalance and uncheck it.
Project Structure
The addin has a very simple project structure, a main (connect.cs) class, a controller class, two menu item classes and the SSMSAdin.addin XML file that we will deploy into the Addin folder. There is a partial class to separate out the events and not clutter up the main Connect.cs class, as we will only be using a couple of the main events.
Code Explanation
Create ObjectExplorer Context & CommandBar Menuitems
One of the IDTExtensibility2 member methods you will implement is the OnConnection. This is the starting point of your Addin. The snippet of code below, creates the new Tools menuitems and an event handler for when the ObjectExplorer action has changed (i.e. clicking on a table or a column - this event will capture that action), thus allowing you to control what context menu should be displayed for a particular ObjectExploer tree item.
public void OnConnection(object application, ext_ConnectMode connectMode, object addInInst, ref Array custom)
{
applicationObject = (DTE2)application;
addInInstance = (AddIn)addInInst;
try
{
ContextService contextService = (ContextService)ServiceCache.ServiceProvider.GetService(typeof(IContextService));
contextService.ActionContext.CurrentContextChanged += ActionContextOnCurrentContextChanged;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
if (connectMode == ext_ConnectMode.ext_cm_UISetup)
{
object[] contextGUIDS = new object[] { };
Commands2 commands = (Commands2)applicationObject.Commands;
string toolsMenuName = "Tools";
Microsoft.VisualStudio.CommandBars.CommandBar menuBarCommandBar = ((Microsoft.VisualStudio.CommandBars.CommandBars)applicationObject.CommandBars)["MenuBar"];
CommandBarControl toolsControl = menuBarCommandBar.Controls[toolsMenuName];
CommandBarPopup toolsPopup = (CommandBarPopup)toolsControl;
try
{
cmdKillTrans = commands.AddNamedCommand2(addInInstance, "KillTrans", "Kill Transactions", "List all transactions that can be stopped", true, 9, ref contextGUIDS, (int)vsCommandStatus.vsCommandStatusSupported + (int)vsCommandStatus.vsCommandStatusEnabled, (int)vsCommandStyle.vsCommandStylePictAndText, vsCommandControlType.vsCommandControlTypeButton);
if ((cmdKillTrans != null) && (toolsPopup != null))
{
cmdKillTrans.AddControl(toolsPopup.CommandBar, 1);
}
}
catch (System.ArgumentException ex)
{
MessageBox.Show(ex.Message);
DebugMessage(ex.Message);
}
try
{
cmdServerInfo = commands.AddNamedCommand2(addInInstance, "ServerInfo", "Server Information", "List server\\product information", true, 5, ref contextGUIDS, (int)vsCommandStatus.vsCommandStatusSupported + (int)vsCommandStatus.vsCommandStatusEnabled, (int)vsCommandStyle.vsCommandStylePictAndText, vsCommandControlType.vsCommandControlTypeButton);
if ((cmdServerInfo != null) && (toolsPopup != null))
{
cmdServerInfo.AddControl(toolsPopup.CommandBar, 2);
}
}
catch (System.ArgumentException ex)
{
MessageBox.Show(ex.Message);
DebugMessage(ex.Message);
}
try
{
commandBackUp = commands.AddNamedCommand2(addInInstance, "BackupInfo", "Backup Information", "List backup information", true, 8, ref contextGUIDS, (int)vsCommandStatus.vsCommandStatusSupported + (int)vsCommandStatus.vsCommandStatusEnabled, (int)vsCommandStyle.vsCommandStylePictAndText, vsCommandControlType.vsCommandControlTypeButton);
if ((commandBackUp != null) && (toolsPopup != null))
{
commandBackUp.AddControl(toolsPopup.CommandBar, 3);
}
}
catch (System.ArgumentException ex)
{
MessageBox.Show(ex.Message);
DebugMessage(ex.Message);
}
}
}
ObjectExplorer Event Handler
The event below id setup in ther OnConnection method above, thus we can capture each ObjectExplorer change. We make use of the static variables to stop the context menuitems from being created multiple times.
NB: UserTables is the InvariantName for Tables.
private void ActionContextOnCurrentContextChanged(object sender, EventArgs e)
{
try
{
INodeInformation[] nodes;
INodeInformation node;
int nodeCount;
IObjectExplorerService objectExplorer = (ObjectExplorerService)ServiceCache.ServiceProvider.GetService(typeof(IObjectExplorerService));
objectExplorer.GetSelectedNodes(out nodeCount, out nodes);
node = nodeCount > 0 ? nodes[0] : null;
if (node != null)
{
if (node.Parent.InvariantName == "UserTables")
{
if (!IsTableMenuAdded)
{
_tableMenu = (HierarchyObject)node.GetService(typeof(IMenuHandler));
SqlTableMenuItem item = new SqlTableMenuItem(applicationObject);
_tableMenu.AddChild(string.Empty, item);
IsTableMenuAdded = true;
}
}
else if (node.Parent.InvariantName == "Columns")
{
if (!IsColumnMenuAdded)
{
_tableMenu = (HierarchyObject)node.GetService(typeof(IMenuHandler));
SqlColumnMenuItem item = new SqlColumnMenuItem(applicationObject);
_tableMenu.AddChild(string.Empty, item);
IsColumnMenuAdded = true;
}
}
}
}
catch (Exception ObjectExplorerContextException)
{
MessageBox.Show(ObjectExplorerContextException.Message);
}
}
Disconnecting
We need to perform house-keeping when disconnecting from SQL Server and remove the newly added menu items. They will be loaded again when SQL Server starts, as SQL Server looks in the Addin folder.
We remove them because, you can manually go in and delete the Addin file and they will still be hanging around in SQL Server (for ever!!!).
public void OnDisconnection(ext_DisconnectMode disconnectMode, ref Array custom)
{
switch (disconnectMode)
{
case ext_DisconnectMode.ext_dm_HostShutdown:
case ext_DisconnectMode.ext_dm_UserClosed:
if ((cmdServerInfo != null))
{
cmdServerInfo.Delete();
}
if ((commandBackUp != null))
{
commandBackUp.Delete();
}
if ((cmdKillTrans != null))
{
cmdKillTrans.Delete();
}
break;
}
}
Context Menu Click Events
The click event for the duplicate context menu item will retrieve the database, table and column name and build up the SQL statement. Display them in a new query window and execute it.
private void Duplicate_Click(object sender, EventArgs e)
{
ToolStripMenuItem item = (ToolStripMenuItem)sender;
bool generateColumnNames = (bool)item.Tag;
Match match = columnRegex.Match(this.Parent.Context);
if (match != null)
{
string columnName = match.Groups["Column"].Value;
string tableName = match.Groups["Table"].Value;
string schema = match.Groups["Schema"].Value;
string database = match.Groups["Database"].Value;
string connectionString = this.Parent.Connection.ConnectionString + ";Database=" + database;
string sqlStatement = string.Format(SSMSAddin.Properties.Resources.SQLDuplicateColumnData, columnName, tableName);
this.dteController.CreateNewScriptWindow(new StringBuilder(sqlStatement));
this.applicationObject.ExecuteCommand("Query.Execute");
}
}
We can see that there is in deed duplicate artists, if we do a select from the table - when we execute the above duplicate query, it will leave one unique artist.
Testing\Running Addin Within SQL Server 12
Because we have set SSMS as the external application to test with (project properties), SSMS will startup automatically and we can step through (F10) the debugger.
NB: Remember to copy your .Addin file to one of the deployment folders, and the XML element Assembly references our Debug assembly.
Below, are examples of each of the menu items we have created.
Table Context Menu
Table Count
Generate Insert Script
Column Context Menu
As we have seen in the previous section, there is a column context menu item. Notice the commented out lines, these can be uncommented out when you are confident in executing the SQL statement.
Global - Command Bar Menu
From the Tools menu, we can see the newly created (global acting) SQL statements. I have included some common SQL statements here, like Killing Transactions, Server Information and displaying Backup Information.
Below, I have executed the Server Information menu item, this will provide general information regarding your SQL Server.
SQL Server 12 Version Details
Below are the version details of the SQL Server 12 I used to develop the Addin against (standard enough).
Points of Interest
Now that you have seen how to reference tables or columns, it is relative easy to extend your custom Addin to perform more complex SQL statements. For example, when two columns are selected, there could be an option to select everything based on a join (as you can execute a SQL statement to find the FK's and then dynamically build up the SQL).
Useful Links
