SSMS Query Shortcuts

I've recently made a presentation for that topic and decided to post some of it's materials here.

*That post is going to improve your working experience with SSMS.

Native SSMS Functional Shortcut Keys

If you work with SQL Server for a long time you've probably learn some Keyboard combinations to speed up your administration or development process.
The full list of SSMS Shortcut keys you can find in MSDN

I will try to re-categorize the most interesting ones:

The most popular are:
F5 - Execute selected query or All queries within the current Tab;
Ctrl-R - Show/Hide Result Panel
Ctrl-Tab - Switch between tabs;

Windows wide ones:
Ctrl-N - Create new Tab;
Ctrl-S - Save Current tab to a file;
Ctrl-O - Open File or Project;
Ctrl-F / Ctrl-H - Find and Replace text within a Document, a Project or in all open Tabs;
Ctrl-Z / Ctrl-Y - Undo/Redo;
Ctrl-A - Select All;
Ctrl-C/Ctrl-V/Ctrl-X - Copy/Paste/Cut;

Less popular, popular but very handy:
Ctrl-Shift-U / Ctrl-Shift-L - Change selected text to upper case or lower case;
Ctrl-K+Ctrl-C / Ctrl-K+Ctrl-U - Comment/Un-comment selected text;
Alt-Break - Cancel Query execution;
Ctrl-L - Generate Estimated Query Execution Plan;
Ctrl-M - Turn ON functionality to Produce Actual Query Execution Plan;
Ctrl-T / Ctrl-Shift-F / Ctrl-D - Switch between output to Text, File or Grid;
Ctrl-J - List Members from IntelliSense;
Ctrl-F5 - Parse selected Query or All queries within the current Tab;
Ctrl-F6 / Ctrl-Shift-F6 - Cycle between Code, Grid Result and Messages;

Undocumented/Incorrectly Documented:
Ctrl-U - Brings active cursor to database selection;

If you fill like I missed something interesting, please let me know it to include in the list.

Native (not-changeable) SSMS Query Shortcuts.

That category includes only 3 Shortcuts, which run 3 stored procedures:

Alt-F1 - sp_help
- With no parameters returns two data sets: List of all objects with schema and type and User Defined Data types.
That list has a huge problem, it does not report actual objects' schemas and Microsoft is not going to fix it (See budg report: https://connect.microsoft.com/SQLServer/Feedback/Details/674733)
- If you select a particular object it will return minimum information about the object, such as "Creation Time". Exception is only Tables, info about Tables is awesome.

Ctrl-1 - sp_who
-  With no parameters returns very limited list of currently connected SQL Server sessions
- With selected "Login Name", returns only list of connections for that "Login Name"
I'd rather use sp_who2, it returns little bit more info.

Ctrl-2 - sp_lock
-  With no parameters returns very limited list of current locks in SQL Server.
- Accepts up to two Session IDs as parameters to filter on.

Not Native (Changeable) SSMS Query Shortcuts.

In this section I will list shortcuts which I use in my SSMS Studio.
You can download scripts from here: SSMS Query Shortcut Scripts
* These scripts are mostly developed by bright minds like: Glen Berry, Paul Randal, Adam Machanic, Ben NAvarez and others, but usually reverted from their original versions.
** Some scripts are developed by myself and I can accep all blame on them.
*** These query shortcuts are live and breathing. I'm constantly add new features, fix old bugs and adjust their functionality.

Here is their description:

Ctrl-F1 - Tables

Procedure "Ctrl-F1" returns following, depending on parameters:
1. No parameters: List of all user tables in current databases sorted by size and number of records.
    It is normal that tables with less records are bigger in size.
2. "Name of a table": "LIMITED" statistics for all indexes on that table.
    For large tables that operation might run for several minutes.
3. "Name of an index": "LIMITED" statistics for that index along with list of Page allocation.
4. "Name of a table or an index" + statistic mode: SAMPLED or DETAILED.
Example 1: tbl_MyTable
Example 2: 'CLUIX_MyTable','DETAILED'

Ctrl-3 - Locks (Similar to sp_lock)

Procedure "Ctrl-3" returns following, depending on parameters:
1. No parameters: List of all current Locks. Similar to sp_lock with some extentions.
2. "Database ID": List of all current Locks for the specified Database.
3. "Session ID": List of all current Locks for the specified session.
 That is possible that "Session ID" and "Database ID" cross each other and wxtra results are returned.
4. "Database Name": List of all current Locks for the specified Database.
5. "Table Name": List of all current Locks on that table.
    Have to be a table in the current database or with fully qualified name.
6. "Object Id": List of all current Locks on that object.
7. "TAB", "PAG", "DB": List of all current Locks for specified type of an object.
8. "X", "U", etc. : List of all current Locks with specified mode.
9. "IP" or "Host Name" or "Login Name" or "Application Name": List of all current Locks for specified source.
 That option might match table or database name.
Example 1: tbl_MyTable
Example 2: TAB
Example 3: 'Sch-M'
Example 4: 123456789

Ctrl-4 - Indexes

Function Ctrl-4: SQL Server Index Troubleshooting.
1. No options: Returns Lists of TOP 10 Not-Used Indexes and TOP 10 Missing Indexes.
   Disclosure A: Both lists are valid only after full regular business workload cycle. Without SQL Server Reboots and Index Maintenance.
   Disclosure B: Before applying Missing Indexes, always check if any similar index exist. Maybe you can just simply modify an existing one.
2. Index Name: Returns Index Statistics and general info.

 Ctrl+5 - Databases

Function Ctrl-5: SQL Server Database Details.
1. No options: Returns List of all Databasese and their files with current sizes and used space.
2. Database Name: Returns sizes and used space for specified DB and Temp db. Lists of all DB Settings.

Ctrl+6 - Performance Troubleshooting

Function Ctrl-6 Options: SQL Server Current Activity.
1. No options: Returns List of currently running tasks, list of all user opened trnsactions and Current Waits stats.
2. Letter "B": Returns only running blocking chains.
3. Letter "S": Returns data in simple mode without Quwery Plan.
4. Letter "W": Returns Only Current Waits stats.
5. Letter "W" + Number of seconds : Returns Waits Stats Delta. (Max=999) (Example: "W10" Returns Delta waits within 10 seconds).
6. Letter "M": Concentrate on Memory. Adds Memory allocation per database.
7. Letter "C": Concentrate on CPU Utilization. Adds dm_os_schedulers, dm_os_workers & dm_os_waiting_tasks.

Ctrl+7 - Recent Connection's queries

 Returns information about current sessions.
 Parameters:
 (No Parameters) - Returns full list of current sessions.
 Number - filter by Session ID
 IP Address or "<local machine>" - filter by client Net address.

Ctrl+8 - Query Cache

Function Ctrl-8 Options: SQL Server Query Cache.
1. No options: Returns: % of Adhoc Queries; TOP 1000 List of all queries stored in SQL Server Cache and TOP 10 Working time diagram.
2. "X": Query Plan in XML format. (Might generate an error: "XML datatype instance has too many levels of nested nodes. Maximum allowed depth is 128 levels.")
3. Part of a query: Will search (TOP 1000) for that part of a query within SQL Server Cache.
4. "ORDER BY + field name + ASC/DESC": Adds sorting to the full List of all queries stored in SQL Server Cache. (option #1)
5. sql_hqsh or plan_handle: Returns all plans for particular SQL Query or only one plan for plan_handle

Ctrl+9 - Statistics

Function Ctrl-9 Options: SQL Server Statistics.
1. No options: Returns List of all Statistics in current DB.
2. Table Name: Returns List of all Statistics for specified table (Applicable for SQL Server 2008R2 SP2 or higher).
3. Statistic name: Returns Statistics' Header, Density Vector and Histogram.

Ctrl+0 - Objects

Function Ctrl-0 Options: SQL Server Objects.
1. No options: Returns List of all tables and List all objects in current DB.
    It is normal that tables with less records are bigger in size.
2. Object Name: Object's code or full description.
3. Index/Table name (+ parameter): Gives info about index allocation. Second is reporting mode: SAMPLED or DETAILED.
For system tables requires fully specifired name like: 'sys.objects'.
Does not handle: Synonyms, Defaults, Constraints.
Example 1: tbl_MyTable
Example 2: 'CLUIX_MyTable','DETAILED'

SSMS Query Shortcuts' Implementation

To set these manual shortcuts you have to do following:
1. Press Alt-T + O, which will bring you to SSMS Tools and Options.
2. Go to "Environment -> Keyboard -> Query Shortcuts"

3. Select the shortcut you need and insert the query into second column's cell.

Things to pay attention to:

1. The query script must not have Carriage Return symbol with ASCII code 0x0D. You can put whole script in one line, but it won't be editable well. So, use the PowerShell script included in the package to remove Carriage Return symbol.

2. In order to use newly created or changed shortcut you have to open new Tab in SSMS. All old tabs will not see the change.

3. These scripts create Temporary Stored Procedures and if your user database  has different collation with TempDB database you'll get an error message.

4. If you going to modify my script or write your own, remember the size limit for Query Shortcuts is 32K.

5. The BIGGEST problem of Query Shortcuts that you can't Export/Import them in SSMS.

If you are  going to use Query Shortcutsand also not satisfied by inability to Export/Import them in SSMS please leave your comment in Microsoft Bug Report: "SSMS Query Shortcuts' Import/Export do not work"