For most DBAs, Management Studio is the primary work tool.

That’s why properly using it can save a-lot of time and effort.

Here’s a few tricks that can improve your productivity when working with it:

1. Snippets and Keyboard Shortcuts

The amount of time these two features saved me is unimaginable.

We’ll start with keyboard shortcuts: If you go to Tools -> Options -> Environment -> Keyboard ->  Query Shortcuts, you’ll see the following screen:

When those shortcuts are triggered, they generate a command in the format of “shortcut Stored Procedure” + “Highlighted part from SSMS”.

For instance, if I want to quickly see the details of a table named “Tab1″, I write the table name in the query windows and hit Alt+F1. This will generate the command “sp_help” + “Tab1″.

I like to add sp_helptext and sp_helpindex to this menu. In the same manner, whenever I want to see a programmable object’s text, I write the object’s name in the query windows and hit Ctrl+3, and when I want to check the indexes of a table, I write the table name in the query windows and hit Ctrl+4.

Snippets are an extension to this concept and allow much more options, including programmable capabilities inside them.

I have gathered a series of scripts I use regularly and created a snippet for each one of them. This initial investment saves me a great amount of time every day.

For instance, if I want to check the index usage patterns for a certain table, I write the name of the table, hit Ctrl+K+S, choose “My Code Snippets” and see this:

When I choose “Index usage”, a query targeted at the relevant table will be generated. For this example, the following piece of code will be generated:

select i.name as index_name, s.*
from sys.dm_db_index_usage_stats s
inner join sys.indexes i
on s.object_id = i.object_id and s.index_id = i.index_id
where s.database_id = db_id() and s.object_id = object_id('Tab1')

Snippets save me the most amount of time when I want to see what queries are currently running. I hit Ctrl+K+X, choose “My Code Snippets” and see this:

When I choose “Show Current Running Queries”, the following script is generated:

select
substring(text, statement_start_offset/2+1,
         ((case
            when statement_end_offset = -1
               then datalength(text)
            else
               statement_end_offset end - statement_start_offset)/2) + 1)
          as running_statement,
text as current_batch,
p.query_plan,
r.*
from sys.dm_exec_requests r
cross apply sys.dm_exec_sql_text(sql_handle) t
outer apply sys.dm_exec_query_plan(plan_handle) p

You can imagine it takes more (or a-lot more) time achieving this in other ways.

For more about how to create your own snippets (or download mine), click here.

2. Find & Replace with Regular Expressions

Regular expressions allow us to find & replace more complex patterns than the regular find & replace.

For example, let’s say I have a long list of columns that looks like this:

Now let’s say I want to add a comma between each two items in the list. This can be used in order to generate an insert, a select, etc..

Instead of starting to insert commas manually, I hit Ctrl+H and get to the Quick Replace screen.

Under “Find what” I enter “\n”, and under “Replace with” I write a comma.

Here’s what I get after hitting Replace All (after some adjustments for better visibility):

And now i’m much closer to what I need to achieve.

For more about regular expression options, see MSDN and Phil Factor’s article.

3. Split Screen

Let’s say you look at a very long script, and you want to compare two code sections that are far from each other. Here’s a way to do it efficiently:

At the top hand side of the SSMS screen, you will see a double-sided arrow.

Click on it and pull it down. It will split the screen to two areas, allowing you to scroll each one separately.

Here’s an example (the code is from Adam Machanic’s sp_WhoIsActive):

4. Connection Colors

The purpose of connection colors is to keep you from running something on the wrong server. Obviously it will improve your productivity because you won’t have to spend time fixing it, or worse, searching for a new job.

To set a connection color, open a new connection window. Write the connection details of the server you wish to connect to, and then click options:

Under Connection Properties, mark “Use custom color” and click Select to choose your color:

After your select the desired color, you will see a stripe in that color at the bottom of the SSMS screen each time you connect to the server.

5. Registered Servers

If you manage more than a few servers, you can use the registered servers feature to keep a list of your servers and connect to them quickly when you need.

To create the server list, go to View -> Registered Servers.

Right click Local Server Groups and select New Server Registration:

On the General Tab, enter the server name and authentication details.

Under the Connection Properties tab, among other settings, you can set the connection color. When finished, click Save.

You can also nest servers under groups for better organization. In addition, you can also import and export the server list to other computers.

Follow @MatanYungman