Breeze through SQL Server Management Studio

If you are among the many developers who frequently use SQL Server Management Studio for tasks like writing Stored Proc's, complex queries, views etc, then definitely you will find yourself writing SELECT statements to get a view of data inside tables and views. The next possible statement you might frequently use should be the sp_helptext to view definitions of   user-defined rules,  unencrypted Transact-SQL stored procedures, user-defined Transact-SQL functions, triggers, computed columns, CHECK constraints, views etc.

A small analysis on the count of SELECT or sp_helptext statements issued to view the data or the schema of an Stored Proc\View. I came to find that on an average it was around  30 plus. I got this from SQL Profiler after excluding those statements issued from my application. Before I came to find a solution for this, I kept a copy of frequently used functions so as to copy and paste whenever I need any of those, that's how I was doing before embracing this cool elegant solution. 


I would call myself a lazy programmer, yes you read it right "A lazy programmer, but definitely not a lousy one". Being lazy, I always wanted to get more from little. So from a developer point of view this means, more productivity\output from less keystrokes and writing scripts for those little repetitive tasks like changing IE proxy, Hibernating\Shutting down windows, enabling USB ports (hope you know where this specifically applies to), Recovering  crashed desktops and the list goes on and on.

Here I am going to pull yourself into my lazy programmer band wagon(i.e. more productivity from less keystrokes) while working with SQL Server Management Studio. Before moving on, have you ever used the shortcut "Alt + F1" to view metadata for the selected table name? If yes, you had knowingly or unknowingly used the feature on what we are about to explore.


The solutions I am about to show you are in-built to the editor, so no need to worry about shelling out your  hard earned money nor impress your manager for getting budget allocation to get those new tools. Here you will see a pretty awesome feature called "Keyboard shortcuts" built-in with SQL Server Management studio to get this done. So lets move on to the solution part. 

Fire up your "SQL Server Management Studio" and select "Tools -> Options" from the menu.

In the "Options" widow select Environment then select "Keyboard" which should get you to a screen something like the one below(BTW I am using SQL Server 2008 R2). Here you could find three predefined shortcuts already assigned for the key combinations like Alt+F1, Ctrl+1 and Ctrl+2 this means all those three shortcuts are already taken out by MSFT guys, but we still have a lot many key combinations left out there with ready to occupy boards hanging all over them (that means, those shortcuts with no text in the second column).

Lets start filling up those unoccupied slots with our own set of SQL arsenals. To get the job done, you need to copy the statements(highlighted in green) excluding the quotes to the second column (named Stored Procedure).


Ctrl + 3  'SELECT * FROM ' (make sure there's a single space after the FROM keyword and no quotes before and after)


Ctrl + 4 'sp_helptext '  (make sure there's a single space after the FROM keyword and no quotes before and after)

Click "OK" button and exit the editor. 


Now its time to see those new shortcuts in action. 

1. Fire up the SQL Server Mgmt Studio and open a new query window. 
2. In the toolbar change to the database of your choice. 
3. Key in a table name to the query window and select(highlight by selecting the table name) the table name you had just entered.
4. Press the key combination Ctrl + 3 and you should see all the records in that table getting listed in the results window. If any errors are there make sure you had put a space after the "FROM" keyword and no quotes before and after while entering in the keyboard shortcuts window.
5. So we had successfully saved ourselves from repeatedly typing "SELECT * FROM" each time to view records inside a table.
6. Next we will try out the other key combination Ctrl + 4. For this enter a stored proc name to the query window and select the SP name and press Ctrl + 4. You should be seeing the body of the SP, i.e SP definition including parameters and all the t-sql code contained in the SP. In case if the output is displayed in a datagrid(which is the default behavior), you can switch to text view by pressing Ctrl + T and re-issuing the Ctrl+4 shortcut. This time you should be seeing the output like in a text editor. To switch back to grid view press Ctrl +D

So I am leaving out the other shortcuts for you to fill in with the most common statements of your choice. So next time onwards the new keyboard shortcuts should do all the magic for you without the need to keyin SELECT or sp_helptext statements and also start getting used to the  Lazy Programmer experience.