Wednesday 18 March 2015

TOAD for SQL Server - Auto Complete / Auto Replace new entry not working fix

Toad for SQL server is an alternative to SQL Server management Studio (SSMS). Its a free tool from Quest Software which is now part of DELL. Its still a debate among developers over which is the best SQL Management tool for SQL Server. We will not get into it today. Instead, for those of you who are using Toad for SQL Server we will check how to make most out of the tool.
Today we will look into AUTO REPLACE option in Toad, which can also be made use of as a work around for AUTO COMPLETE of custom code snippets.

For example, typing
"SSF" automatically expands to "SELECT TOP 100 * " and
"NL" to "WITH(NOLOCK)".
It would greatly increase your productivity. To achieve this we will make use of the AUTO REPLACE option in TOAD. But the problem is that in TOAD free version, you cannot make new AUTO REPLACE entries. A work around is to manually edit the template file for AUTO REPLACE.

Follow the below steps:


1) Get to the Options window from menu - Tools > Options.
Tools > Options

2) In the Options Window, go to Editor > Auto-Replace.
You can see the Auto Replace options available.
But if you try to add new it wouldn't get saved.
Auto Replace Editor Window
3) Browse to the folder: "C:\Program Files (x86)\Quest Software\Toad for SQL Server Freeware 6.5\Templates\CodeTemplates".
Toad Auto-Replace template folder

4) Open the SQL template file: CodeTemplate.SQL.SqlServer.xml.
You can add your auto complete snippets here.
Ex. Line 8 and 9:
<Template Title="st100">SELECT TOP 100 * </Template>
<Template Title="nl">WITH(NOLOCK) </Template>
From the above example typing NL will get replaced automatically by WITH(NOLOCK).
CodeTemplate.SQL.SqlServer.xml file
5) Restart Toad Editor and the new changes must start to work for you.

Happy Coding.....

No comments:

Post a Comment