January 19th, 2012

Run Your Custom Script Quickly with SSMS Tools Pack

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.
Website Link
Download Link

Steps:

Suppose you want to see total number of records in different tables.

1. Click on SSMS Tools > “Run Custom Script” > Options

SSMS tool pack TechBrij SQL Server

2. Left side of dialog, Enter Shortcut ‘Total Records‘ and check Run checkbox

SSMS Tools Pack TechBrij SQL Server

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.

SSMS Tools Pack TechBrij SQL Server

Click on Save and OK button.
5. Now, Right click on any table > “Select SSMS Tools” > Run Custom Scripts > Total Records

SSMS Tools Pack TechBrij SQL Server

You will get Total Records in result and no need to pass table name in query again and again.

SSMS Tools Pack TechBrij SQL Server

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.