Prashanth Jayaram

Custom keyboard shortcuts in SQL Server Management Studio (SSMS)

March 27, 2017 by

People love taking shortcuts because it’s the easiest way to do things faster; computers are no exception. Shortcuts, particularly ones performed by keyboard, can save you hours of time once applied properly. Learning keyboard shortcut saves you a lot of time; you’ll definitely notice a boost the productivity because you’re not unnecessarily reaching for a mouse all the time.

I’ve rounded up some of the most common DBA customized keyboard shortcuts in SSMS. The same set of rules can be applied to define one which you feel is most important in your day to day activities. Stay on board and you’ll be mastering these tricks in no time!

Keyboard shortcuts are vital and important for anyone who wants to perform an effective work. It’s vital to understand the ins and outs of the IDE as it saves a lot of time. SQL server Management Studio bundled with lots of keyboard shortcuts and I’ve added a useful reference go through in reference section.

In this article, we will explore the most essential user defined and customized shortcuts which will make our daily life more easy and intuitive. The use of SQL Server Management Studio let the users define and design the customized short key to perform various day to day activities. This article outlines the tips and tricks that one can apply to perform effective work with minimal efforts

We are going to see how the custom queries can be used as query shortcuts in SSMS. There are plenty of the resource are available to discuss the default shortcuts that are bundled with SSMS IDE but the following section talks about customization and parameter passing through keyboard shortcuts under various scenarios is discussed in detail.

If you are a Developer/DBA we always tend to achieve the results in simple and smarter way. This is where we are going for keyboard shortcuts. We do have an option available in SSMS to set and define the rules to use the keyboard shortcuts.

The below the steps to create customized key board shortcuts

Open SSMS->Go to Tools -> Select Options.

Go to Environment -> Keyboard -> Query Shortcuts

On the right, in the query shortcuts pane, we can see some shortcuts which are by default in SSMS. Now, if you need to add a new one, just click on any column under Stored Procedure column. In this case we have added ctrl+3 for sp_helptext. The sp_helptext is a procedure that displays the definition of objects.

Click OK. Now go to a query window and select the stored procedure then press CTRL+3, it will show the script of the selected stored procedure aka DDL script which is shown below

Let’s consider a scenario to display the definition of non default schema objects.

The below section talks about viewing the definitions of non default schemas, other than dbo schema object, is explained. In such cases, you need to select the object and schema within a single quote to make this work.

The enclosing of schema.object name in a single quote is applicable to all scenarios where you tend to perform any manipulation using non default schema objects

Keyboard Shortcut – Passing Parameter to Query using sp_executesql

Let’s prepare a SQL query which accepts input parameter through sp_executesql. The object ‘configuration_properties’ is the input to the SQL. If the input parameter schema is non-default schema then the object should enclose within in a single quote along with a schema name.

The below SQL is used to fetch rowcount and index details of the object

After successful execution, the SQL is ready for the customization

Now,

  • Prepare the SQL into one single line i.e. is bring all the statement into a single line as the text parameter accepts only single line in the Query Shortcuts pane
  • Copy the SQL text until the “=” sign

The actual SQL is copied under Ctrl+9. The ctrl+3 are a similar SQL. The idea is just to show you an example.

Now, please go ahead and select the table name from the query window and press CTRL+9 (the key we selected).

This section deals with getting the fragmentation details using DMF . The @objname parameter is passed as an input to DMF.

The simple SQL is given below

Let’s group all the SQL statement into single line and copy the SQL until “=” sign and paste in a query shortcut pane in the available shortcut key.

Now, go ahead and select the table name from the query window and press CTRL+5 (the key we selected) to get the required fragmentation details

The most commonly used SQL to fetch the database detail using Keyboard short in SSMS discussed below

Prepare the multiple line SQL statements into single line using SSMS Regular Express update or any available tools such as Textpad or Notepad++ to replace carriage return “\r” with blank value will yield the result into single liner. It’s really easy to merge the SQL into a single line using text editor tools. Copy and paste the one liner SQL into the Query Shortcut pane for the available shortcut keys (ctrl+5)

Now, open a new window and press CTRL+5 (You can select any key as per the definition from the Query shortcut) to get the below result

There are few more examples that you can try using Keyboard shortcuts and are given below

To Get SQL File details

In the below example, PowerSQL is the database name and input parameter. Change the parameter as per your requirement

Simple query to fetch database information

Space usage details of a table

In this case the DiskVolume is the name of the table. In case of passing non-default schema object then make sure to enclose it in a single quite along with schema.object name

For non-default schema object

Space usage details of all the databases

The below script doesn’t need any input parameter

Now, create a new query window and press CTRL+4 (the key we selected) to get the sp_spaceused details of databases

Conclusion

Once you learn the art of remembering and using existing and building the customized shortcuts you’ll definitely notice a boost to productivity because you’ll not be unnecessarily reaching out for the same routine tasks.

See more

To boost your SQL Server development productivity, check out Free SQL Server Management Studio add-ins.

References

 

Prashanth Jayaram

Prashanth Jayaram

I’m a Database technologist having 11+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application.

My specialty lies in designing & implementing High availability solutions and cross-platform DB Migration. The technologies currently working on are SQL Server, PowerShell, Oracle and MongoDB.

View all posts by Prashanth Jayaram
Prashanth Jayaram
SQL Server Management Studio

About Prashanth Jayaram

I’m a Database technologist having 11+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application. My specialty lies in designing & implementing High availability solutions and cross-platform DB Migration. The technologies currently working on are SQL Server, PowerShell, Oracle and MongoDB. View all posts by Prashanth Jayaram

1,819 Views