There are so many features in SQL Server Management Studio, we may not know or use all of them. These are some small & interesting features that I discovered and wanted to jot them down.Tip #1: Open Object Explorer and new query on start up
- SQL Server Management Studio combines the features of Enterprise Manager and Query Analyzer of SQL Server 2000. If you have used SQL Server 2000 Query Analyzer a lot in the past, you will surely notice that the query window does not open on start up. You can of course press Ctrl + N to open a new query window, but there is a much better option to have a new query window opened for your by default on starting up. Go to Tools
. Select the Environment
option in the treeview and from the At startup
drop down on the right, select "Open Object Explorer and new query".
From the next time on, a new query window will automatically open when you start a new instance.Tip #2: Screen tips & Shortcut Keys -
By using keyboard shortcuts to reach frequently used features, you can not only cut down on time but also dazzle co-workers with your speed. If you don't normally use keyboard shortcuts, there is a simple way to get acquainted with icons on the toolbar and their related shortcut keys.
Select the Show Screentips on Toolbars
& Show shortcut keys in ScreenTips
checkboxes from the menu option Tools
If you like keyboard shortcuts, check my compilation of SSMS shortcutsTip #3: Configure Shortcuts -
"SELECT * FROM tablename" is possibly the most used query for many developers. The next tip derived from a older SQL Server 2000 article
, helps you save typing "SELECT * FROM " every time you need to see a table listing. Go to the menu option Tools
and in the treeview expand Environment
and select Keyboard
underneath it. In the Query Shortcuts
section, you can assign your own stored procedures to specific key combinations.
The image above shows how I have configured a custom stored procedure sp_SelectFromTable. You can grab the code for sp_SelectFromTable from the link mentioned above.
Whenever I have to view rows of a particular table, I drag the table from Object Explorer onto the query window or type just the table name manually, select that name & hit Ctrl + 3. If most of the tables you handle are large, you can tweak that stored procedure to show you just the top 10 or 25 rows to quickly see a few.
You can also utilize this nifty stored procedure to find a Column or a Table in a database given the full name or partial name as input
, through a shortcut.Tip #4: Filter objects -
If your Table or Stored Procedure list is large, you often lose several seconds trying to locate a specific object. Thankfully you can set Filters to zero-in on what you are looking for. Let's say you want to look for tables related to Sales. You can right click on Tables under the database you are working in Object Explorer to invoke Filter
> Filter Settings
from the context menu. In the dialog box that opens up, type Sales in the Value
column against the Name
property while keeping the Operator
You can filter database objects including Tables and Stored Procedure by Name
& Creation Date
. There are a host of Operators
in the Filter Criteria to fine tune our search.
It is not immediately obvious that there are a list of Operators for each property. Only when you move to that cell, a dropdown appears with other available options. Tip #5: Select adjacent columns or rows -
Occasionally you may want to select only certain columns or rows from the Results
pane after you execute a query. You can select adjacent columns or rows by keeping Ctrl and Alt keys pressed while selecting the desired columns or rows.Screenshots generated with free tools, Cropper & Paint.NET