Free Online Courses for Software Developers - MrBool
× Please, log in to give us a feedback. Click here to login
×

You must be logged to download. Click here to login

×

MrBool is totally free and you can help us to help the Developers Community around the world

Yes, I'd like to help the MrBool and the Developers Community before download

No, I'd like to download without make the donation

×

MrBool is totally free and you can help us to help the Developers Community around the world

Yes, I'd like to help the MrBool and the Developers Community before download

No, I'd like to download without make the donation

SSMS Tools Pack in SQL Server

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.

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:

SSMS Tools Menu

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:

Toolbox Restore Last Session Option

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:

Regions Minimized

Figure 3: Regions Minimized

Run Custom Script option lists some scripts that we can run. Here are some examples:

Custom Script Option

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):

Search Table/View Data

Figure 5: Search Table/View Data

Ayad Boudiab

Senior Application Developer / Technical Trainer



Senior Software Engineer with experience in C#, JavaScript, jQuery, Knockout JS, Kendo UI, HTML5, and SQL Server. Ayad has more than 20 years experience in technical training and teaching. Contributor to MrBool for about 7 years.

What did you think of this post?
Services
[Close]
To have full access to this post (or download the associated files) you must have MrBool Credits.

  See the prices for this post in Mr.Bool Credits System below:

Individually – in this case the price for this post is US$ 0,00 (Buy it now)
in this case you will buy only this video by paying the full price with no discount.

Package of 10 credits - in this case the price for this post is US$ 0,00
This subscription is ideal if you want to download few videos. In this plan you will receive a discount of 50% in each video. Subscribe for this package!

Package of 50 credits – in this case the price for this post is US$ 0,00
This subscription is ideal if you want to download several videos. In this plan you will receive a discount of 83% in each video. Subscribe for this package!


> More info about MrBool Credits
[Close]
You must be logged to download.

Click here to login