In this article, we are continuing our discussion of applications and add-ons that make developing applications simpler. Today’s discussion is about SSMS Tool Pack. This pack is an add-on to SQL Server (in other words, you need to have SQL Server installed first). The tool can be downloaded from http://www.ssmstoolspack.com. Current version is 2.1. Once you click on the link in the download tab you can download and install that tool. The installation steps are simple: just run the installer, follow the wizard and accept the terms and conditions.
When the tool is successfully installed, you will end up with a menu called “SSMS tools” in SQL Server. The menu should like the following:
Figure 1: SSMS Tools Menu
This main menu mostly contains options that you can manipulate based on your needs. So this is not the place where you can take actions like scripting a table. You set your options here, and the actions are taken when you right click on a table. For example, one of the options is “SQL History”. You can use this to specify how often you want files, databases and tabs to be saved (every 2 minutes…). You can also indicate the location where these files are saved. You can always use “Restore Defaults” button to come back to the default values. As the name implies, with SQL History you can always view the history of the session or the current window.
One of the options that I find very helpful is the “Restore Last Session”. This option is on the toolbox:
Figure 2: Toolbox Restore Last Session Option
Let’s say you are working on a remote server, and you took a break. When you come back to resume your work, you notice that you have been logged off from the server. When you log back in and restart SQL Server, you will be able to click on this button and restore the session you were working on instead of starting back from scratch.
The second option has to do with debug sessions and regions. I find the regions handy when writing large scripts. We can divide the script into regions depending on the business needs. The shortcut to create a region is Ctrl+K, Ctlr+M. These regions can be minimized as you see in the following screen:
Figure 3: Regions Minimized
Run Custom Script option lists some scripts that we can run. Here are some examples:
Figure 4: Custom Script Option
You can click at the bottom of the list and type the command of your choice. Look at the other options to see the way these commands are structured. Now that we have the commands, how can we rum them? If you open a database (like AdventureWorks), right click on a table and select SSMS Tools, Run Custom Scripts and select the script you want to run. The “space used” by a table is a good example.
One of the handiest features is the CRUD Generator. Again, in this menu we set the options for the CRUD, but the way to run them is by right clicking on an object (like a table) and generate the CRUD operations from there. In this case I right clicked on the Person table in the Person schema, selected the SSMS Tools menu and then Generate CRUD. I got the list of Select, Insert, Update, and Delete procedures. Here is an example of the Select procedure:
Listing 1: Select Procedure
IF OBJECT_ID('[Person].[usp_PersonSelect]') IS NOT NULL BEGIN DROP PROC [Person].[usp_PersonSelect] END GO CREATE PROC [Person].[usp_PersonSelect] @BusinessEntityID INT AS SET NOCOUNT ON SET XACT_ABORT ON BEGIN TRAN SELECT [BusinessEntityID], [PersonType], [NameStyle], [Title], [FirstName], [MiddleName], [LastName], [Suffix], [EmailPromotion], [AdditionalContactInfo], [Demographics], [rowguid], [ModifiedDate] FROM [Person].[Person] WHERE ([BusinessEntityID] = @BusinessEntityID OR @BusinessEntityID IS NULL) COMMIT GO
As you can see these procedures are very handy when working with line of business applications that need to select, insert, update and delete items in the database.
Now that we have the tables and we need to back the data up in scripts, how about creating insert statements? We can store these statements in a safe place, and run them when needed. In this case, I right clicked on the Culture table and generated the insert statements. I ended up with the following:
Listing 2: Inserting Statements
BEGIN TRANSACTION; INSERT INTO [Production].[Culture]([CultureID], [Name], [ModifiedDate]) SELECT N' ', N'Invariant Language (Invariant Country)', '20020601 00:00:00.000' UNION ALL SELECT N'ar ', N'Arabic', '20020601 00:00:00.000' UNION ALL SELECT N'en ', N'English', '20020601 00:00:00.000' UNION ALL SELECT N'es ', N'Spanish', '20020601 00:00:00.000' UNION ALL SELECT N'fr ', N'French', '20020601 00:00:00.000' UNION ALL SELECT N'he ', N'Hebrew', '20020601 00:00:00.000' UNION ALL SELECT N'th ', N'Thai', '20020601 00:00:00.000' UNION ALL SELECT N'zh-cht', N'Chinese', '20020601 00:00:00.000' COMMIT; RAISERROR (N'[Production].[Culture]: Insert Batch: 1.....Done!', 10, 1) WITH NOWAIT; GO
If, by mistake, I delete the records from the Culture table, I can use the insert statements above and get the data back.
When you click on New Query to start a new query, do you like to have some SQL automatically added for you? The New Query template helps you do just that. When clicking on New Query (and after installing SSMS Tools) you will see the following:
Listing 3: New Query
BEGIN TRAN ROLLBACK
This pair of statements is important because if you are working in a production database, changing the data will be automatically rolled back for safety reasons. If you need to commit the data, you will specifically write the commit command. Of course, this pair of statements is what comes with the tool. In the SSMS Tools menu, you can change the options and write any script that you want (you can even commit the data instead of rolling back). If you find yourself consistently needing a SQL statement, you can place it in the New Query Template options dialog box and have it generated every time you create a new query. This is a big time saver.
The SQL Snippets options list the shortcuts that you can use to generate statements. You can import and exports snippets. When typing one of these options and hit the enter key (you also can change that in the options to use the tab key), the corresponding statement will be generated. For example, if I type INS and hit the enter key, I will get the following:
Listing 4: SQL Snippets
INSERT INTO <>() VALUES (<>)
There is also search feature that comes with the tool. Right click on a table and select “Search Table/View Data”. You will end up with the following dialog where you can type the text you are looking for (notice the drop down for the search type):
Figure 5: Search Table/View Data
Senior Application Developer / Technical Trainer