sp_QueryShortcuts - setup and how to use them
Weather you're a DBA or Developer this set of shortcuts for SQL Server Management Studio will make your life hell of a lot easier. It will take you some time to get used to them but once you do it will be worth it, I can't imagine working without those anymore. Project was inspired years ago when i saw a similar usage of a stored procedure in SSMS query shortcuts. It was used for a specific database and wasn't very flexible, so i decided to pick it up and work on it, improve it and make it more accommodating and user friendly, so that anyone can use it anywhere.
I would appreciate some feedback and constructive criticism that would help improving sp_QueryShortcuts.
Download
Latest version of the script always available HERE
Setup
First thing that we have to do is go to our SQL Server Management Studio studio options and assign sp_QueryShortcuts stored procedure to each shortcut.
Steps:
I would appreciate some feedback and constructive criticism that would help improving sp_QueryShortcuts.
Download
Latest version of the script always available HERE
Setup
First thing that we have to do is go to our SQL Server Management Studio studio options and assign sp_QueryShortcuts stored procedure to each shortcut.
Steps:
- Go to Tools // Options
- Then Environment // Keyboard // Query Shortcuts for SSMS 2012+ or Environment // Keyboard if you are using SSMS 2008
- For each shortcut enter "EXEC sp_QueryShortcuts #, " where # is the number of the shortcut, remember to leave the comma at the end:
- Restart SSMS
- Run sp_QueryShortcuts_Setup on each server that you want to use the shortcuts
Setup is going to create 3 stored procedures in your master database
- [dbo].[sp_helptext2] by Ranga Babu - to generate script for programmability objects
- [dbo].[sp_WhoIsActive] by Adam Machanic - v11.17 (2016-10-18), I don't think I need to explain this one
- [dbo].[sp_QueryShortcuts] - Shortcuts themselves
Usage
The idea is simple, highlight a text in SSMS text editor and hit the shortcut, highlighted text is passed as a parameter to sp_QueryShortcuts:
You can also add a second parameter to selected shortcuts:
Because of how the SQL is validating syntax, I've decided to use @ and # characters to perform certain functions when included in the highlighted text.
@ - at sign will work like a (dot) when separating schema name from object name i.e. 'hr@Users' will be treated as 'hr.Users'
# - hash sign will work like a % wildcard i.e. 'ab#xyz' will be treated as 'ab%xyz'.
For example we want to use CTRL+5 to find all objects WHERE ObjectName LIKE '%Record%Temperature%'
Shortcuts Description
Here's a short description of all the shortcuts. In future post I will write a bit more on the individual usage and how they work best in combination with others.
CTRL+3 - Select TOP 1000 form highlighted table or view ordered by column 1 descending. As an additional parameter you can pass a column name from that table and it will order the results by that column ascending, if you add a hash sign at the end of column name it will order the results descending. You can pass a value as an additional parameter and the shortcut will add a where clause where column with order 1 equals that passed value.
CTRL+4 - executes sp_help if the object is a user table or sp_helptext2 for all other types
CTRL+5 - performs a search for database objects with highlighted text in the object name. Object type* can be used as an additional parameter.
CTRL+6 - executes sp_WhoIsActive by Adam Machanic. Use 'S' for default values, ‘P’ to include execution plans and ‘D’ to get much more details. (I'm thinking of changing it in the future as I tend to use only one option, most likely it will have some more functionality later on)
CTRL+7 - for a selected table in the firs result set it returns index usage with calculated "index performance". In the second result set it will return information about missing indexes for that table with their "Estimated Benefit".
CTRL+8: performs a search for columns or parameters and returns parent objects names (tables, views, procedures, functions). Object type* can be used as an additional parameter.
CTRL+9 - lists columns or parameters for highlighted object, also converts column list into parameter list that can be copied and used in stored procedures. Object type* can be used as an additional parameter.
CTRL+0: performs a search for highlighted text in programmability objects create script. For a many word text you need to place the text in square brackets i.e. [INSERT INTO Users]. Object type* can be used as an additional parameter.
Have fun and leave a comment!
*SQL Server object types
The idea is simple, highlight a text in SSMS text editor and hit the shortcut, highlighted text is passed as a parameter to sp_QueryShortcuts:
You can also add a second parameter to selected shortcuts:
Because of how the SQL is validating syntax, I've decided to use @ and # characters to perform certain functions when included in the highlighted text.
@ - at sign will work like a (dot) when separating schema name from object name i.e. 'hr@Users' will be treated as 'hr.Users'
# - hash sign will work like a % wildcard i.e. 'ab#xyz' will be treated as 'ab%xyz'.
For example we want to use CTRL+5 to find all objects WHERE ObjectName LIKE '%Record%Temperature%'
Shortcuts Description
Here's a short description of all the shortcuts. In future post I will write a bit more on the individual usage and how they work best in combination with others.
CTRL+3 - Select TOP 1000 form highlighted table or view ordered by column 1 descending. As an additional parameter you can pass a column name from that table and it will order the results by that column ascending, if you add a hash sign at the end of column name it will order the results descending. You can pass a value as an additional parameter and the shortcut will add a where clause where column with order 1 equals that passed value.
CTRL+4 - executes sp_help if the object is a user table or sp_helptext2 for all other types
CTRL+5 - performs a search for database objects with highlighted text in the object name. Object type* can be used as an additional parameter.
CTRL+6 - executes sp_WhoIsActive by Adam Machanic. Use 'S' for default values, ‘P’ to include execution plans and ‘D’ to get much more details. (I'm thinking of changing it in the future as I tend to use only one option, most likely it will have some more functionality later on)
CTRL+7 - for a selected table in the firs result set it returns index usage with calculated "index performance". In the second result set it will return information about missing indexes for that table with their "Estimated Benefit".
CTRL+8: performs a search for columns or parameters and returns parent objects names (tables, views, procedures, functions). Object type* can be used as an additional parameter.
CTRL+9 - lists columns or parameters for highlighted object, also converts column list into parameter list that can be copied and used in stored procedures. Object type* can be used as an additional parameter.
CTRL+0: performs a search for highlighted text in programmability objects create script. For a many word text you need to place the text in square brackets i.e. [INSERT INTO Users]. Object type* can be used as an additional parameter.
Have fun and leave a comment!
*SQL Server object types
| Type | TypeDesc |
|---|---|
| AF | Aggregate function (CLR) |
| C | CHECK constraint |
| D | DEFAULT (constraint or stand-alone) |
| F | FOREIGN KEY constraint |
| FN | SQL scalar function |
| FS | Assembly (CLR) scalar-function |
| FT | Assembly (CLR) table-valued function |
| IF | SQL inline table-valued function |
| IT | Internal table |
| P | SQL Stored Procedure |
| PC | Assembly (CLR) stored-procedure |
| PG | Plan guide |
| PK | PRIMARY KEY constraint |
| R | Rule (old-style, stand-alone) |
| RF | Replication-filter-procedure |
| S | System base table |
| SN | Synonym |
| SO | Sequence object |
| U | Table (user-defined) |
| V | View |
| SQ | Service queue |
| TA | Assembly (CLR) DML trigger |
| TF | SQL table-valued-function |
| TR | SQL DML trigger |
| TT | Table type |
| UQ | UNIQUE constraint |
| X | Extended stored procedure |
Comments
Post a Comment