SSMS - Tips, Tricks and Tweaks

Introduction

SQL Server Management Studio commonly referred to as SSMS is the GUI management application Microsoft provides for querying, configuring, managing and monitoring Microsoft SQL Server.

Since most of us have experience using SSMS for basic object creation and modification, I'm going to start with covering the "built-in" and less commonly known features and progress in future blogs to add-on tools that utilize the extensible feature of SSMS. This is by no means a complete list and I invite you to share below in the comments tools and tips you have found that are useful and feel would be of value for others to know.

Drag and drop scripting

Did you know that you can save yourself some keystrokes when writing a query by dragging an object from the explorer over to the query window? That's right! Check this out!

Write a simple script like you see below and then right click the data base you want in the USE statement, hold it down and drag it over between the brackets.

Release the mouse button and walha! With code completion this example is a little ridiculous, but think about having to type a bunch of column names in a SELECT statement. This can be pretty handy at times and works with anything you see in the Object Explorer!

Scripting Anything

You don't have to know everything about T SQL in order to write a script for creating an object, altering an object, setting or changing a permission or any other SQL server task. SSMS can do it all for you! SSMS works disconnected since server 2005. That means anything you do is not applied until you select save or apply which results in the creation of a script and execution of that script. This feature allows you to save the script for future use instead of immediately applying it. Lets look at an example.

I am going to add to columns to my existing table in my TestDB. Column_1 and Column_2 as you can see have been added in the designer. Instead of clicking the save button and doing it now, I want to wait until tonight so I need a script. Right click in the designer window and select "Generate Change Script...". 

A window will pop up with the script in it for performing the modifications you have made. If the table requires a tear down, there will even be script to move your data to a temporary table. Click the Yes button and it will prompt you for a location and file name to save the script to.

This works for almost anything in SSMS. Modify an index and then script it out so you can apply it later when the server is slow. Make server configuration changes through the GUI and then script them. The list goes on and on.

Protecting Table Modification

I get this question a lot so thought I would throw it in. Did you ever attempt to save a change to a table and have SSMS deny the change? That's a default means of protecting you from doing something that could cause the table to become unavailable or cause data loss. To turn it off and have your way with the server, click the TOOLS menu and then OPTIONS.

Templates

Did you ever have to write a script more than once and wish you had saved it? There are several add-on tools out there we will cover in future articles but SSMS has one that is built in that you can get started with and you can even add your own custom templates as well!

Open SSMS and connect to your test database server. Press CTRL+ALT+T to open your template explorer. You can park it on your side bar so it will open when you mouse over it. I usually have it on the right side.

Open a new query and drag Database.CreateDatabase to the query window.

Press CTRL+SHFT+M and you will be prompted for values to insert into placeholders in the template. Type a database name and hit enter. Your query window will now have a script to create your database.

You can create your own templates and define placeholders for things you do regularly. I'll leave that up to you to research.

Conclusion

Hopefully you have learned some of the interesting and not often discovered features of SSMS. See what else you can discover for yourself by looking around the web or just exploring inside SSMS. If you find something, share! Next BLOG we'll cover add-in tools that will make your life working with SQL server much easier.