Suppose you have to do a task repeatedly with different objects of database, for example, you have to generate code or stored procs of table in the specific format, you created your custom SQL script which takes table name and generate the code. But each time, you have to execute query and pass table name.
What’ll happen If you right click on the table and select your script to execute. It’ll save bunch of time as well as stop you getting irritated :) Let’s see how to do using SSMS Tools Pack.
SSMS Tools Pack:
It is the best free Sql Server Management Studio(SSMS)’s add-in having lots of features like Execution Plan Analyzer, SQL Snippets, Execution History, Search Table, View or Database Data, Search Results in Grid Mode, Generate Insert statements from result sets, tables or database, Regions and Debug sections, CRUD stored procedure generation, New query template..etc.
Suppose you want to see total number of records in different tables.
1. Click on SSMS Tools > “Run Custom Script” > Options
2. Left side of dialog, Enter Shortcut ‘Total Records‘ and check Run checkbox
3. Right Side, Click on ‘Enabled On..‘ option and select ‘table’. It means this will appear on each table.
4. Enter following script
Select Count(*) from |SchemaName|.|ObjectName|
Here |SchemaName| and |ObjectName| are replacement texts. You can see available replacements options on ‘Replacement Texts‘ click.
Click on Save and OK button.
5. Now, Right click on any table > “Select SSMS Tools” > Run Custom Scripts > Total Records
You will get Total Records in result and no need to pass table name in query again and again.
Similarly, you can run your other custom scripts quickly. It is very helpful as a code generation tool. Let me know how you are using it.
There is a new version with a lot of functionalities for SSMS 2012, 2014, 2016, 2017, and 2019, it is also free, it’s called “Michel Max – SSMS Tools”
– Format SQL Code.
– Prepare the Procedures/Functions to be called.
– Regions to organize the code.
– Retrieve the complete Information of a Table.
– Tab Colorize, base in the configuration window you can colorize the existing session based in the filters that you applied, making easy to the developer to identify each server/database he/she is working on.
– Selection Highlight for the same word in the SQL Code.
– Style Markers for the same word in the SQL Code.
– Configurable CRUD creation.
– Copy/Paste Table Structure and Data (Client Side).
– Grid Search, with advanced Extended and Regular Expression.
– Grid Search Highlight.
– Grid Style Markers.
– Grid Export to JSON, Excel XML.
– Grid Image Capture.
– Configurable Header Text Template, with quick insert, so you can easily sign you codes.
– Quick encapsulate code in region.
This is a great article, thank you for the information!
I also want to share my experience, maybe it will be useful for someone too.
I use Developer Bundle to increase productivity, when creating complex queries and code writing