19

Is there any way to implement text editing macros in SSMS? I would, e.g. like to convert the the code as shown below, but with a key-press, not a long-winded regex search and replace.

This:

INSERT INTO [TABLE]
           ([fieldOne]
           ,[fieldTwo])
     VALUES
           (<fieldOne, datetime,>
           ,<fieldTwo, real(24,0))

Must become this:

INSERT INTO [TABLE]
           ([fieldOne]
           ,[fieldTwo])
     VALUES
           (@fieldOne
           ,@fieldTwo)

I know SSMS doesn't natively support this, but I also know that it is extensible, if undocumented, and there is also room for a totally external application that will take copied text, transform it, and paste it back, without having to open an editor, paste, edit, copy, and paste back to SSMS.

Editing the stored templates is not an option, as these templates are dynamically generated, and using Ctrl+Shift+M is not an option either, as I still have to type each parameter name, but without the convenience of copying and pasting in the query editor.

There is no SSMS solution! I am looking for some sort of external voodoo that can help me do this.

ProfK
  • 49,207
  • 121
  • 399
  • 775

6 Answers6

12

What about an AutoHotKey script?

Depending on the complexity of your templates, you could either

  1. use AutoHotKey to play back the keystrokes needed for the regex search and replace, or
  2. copy the template to the clipboard and manipulate it directly within AutoHotKey before pasting it back.

I'm sure the first option will work. I've not tried the second.

This question gives an indication of how an AutoHotKey script can be written to listen for keyboard chords.

Community
  • 1
  • 1
Ed Harper
  • 21,127
  • 4
  • 54
  • 80
7

If you are using SSMS 2005 upwards it has in built support for templates. It isn't exactly full blown macro's, but non the less it is still pretty useful.

The syntax is exactly as you have shown in your first code snippet and you simply press Ctrl+Shift+M to bring up a dialog box that prompts you for the values to go into your bits enclosed in angle brackets.

SQL server generates script in this format if you right click on a table and select "Script Table as" then pick either the insert, update or delete option.

You can also create your own custom templates, or modify one of the existing built in ones (click on View -> Template explorer to get access to the other inbuilt templates).

There is a short article on MSDN that explains how to get started with templates.

KevB
  • 3,570
  • 1
  • 26
  • 29
1

For SSMS 2016 you can use my Visual Commander extension. It supports macros recording/playback and custom C#/VB commands for editor text manipulations.

Sergey Vlasov
  • 26,641
  • 3
  • 64
  • 66
  • Thank you, I'll take a look and I'm sure it will prove useful at some point, but I seldom need a macro in Visual Studio, which your product seems targeted for. It was *SQL Server* Management Studio I was looking for macros for. – ProfK Aug 28 '16 at 15:37
  • @ProfK Yes, Visual Commander was originally developed for VS, but the recent release added support for SSMS 2016 https://visualstudioextensions.vlasovstudio.com/2016/08/26/automate-ssms-2016-with-visual-commander/ – Sergey Vlasov Aug 28 '16 at 18:57
  • Oh brilliant. Installing it now. – ProfK Aug 29 '16 at 05:37
  • Oh no. It won't install; the log file says "This VSIX manifest format is not supported.", then, "VSIXInstaller.NoApplicableSKUsException: This extension is not installable on any currently installed products.". I have SSMS 13.0.15000.23, but an update is available. Maybe I need the update. – ProfK Aug 29 '16 at 05:48
  • 1
    @ProfK Thank you for reporting this problem. Turns out SSMS June 2016 indeed gives this error. You need to update to July or August version. – Sergey Vlasov Aug 29 '16 at 13:17
1

Was looking for something else, but found this question. If you're still looking for something, try SSMS toolpack: http://www.ssmstoolspack.com/ It has macros and a bunch of other neat things. And it's free!

ShadyRudy
  • 29
  • 3
0

I usually copy the code into another editor (Notepad++, or Delphi /RAD Studio editor), do my macro stuff, and then paste it back into SSMS.

Reversed Engineer
  • 1,095
  • 13
  • 26
0

First code is template from old-good MS Query Analyzer. Shortcut to filling is Ctrl+M (but I'm not sure, maybe it is Ctrl+Shift+M).

There should be same feature in newer SSMS.

DiGi
  • 2,528
  • 18
  • 26