• Madeira Team

Five Ways to Improve Your Productivity with SSMS

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:


SSMS Query Shortcuts

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:


SQL Server Snippet

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:

Transact-SQL

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

1

2

3

4

5

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:


SSMS Expansion Snippet

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

Transact-SQL

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

1

2

3

4

5

6

7

8

9

10

11

12

13

14

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:


Column List

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. Don’t forget to mark the checkbox next “Use” and choose Regular expressions.


Replace

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


After Replace

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.