The secrete SQLFile.sql
We are all so used to clicking on the New Query icon (or the keyboard shortcut Ctrl+N). We know it will open a new query window in SSMS, an empty query window that is, but many of us never knew that something else was designed to happen before the new query window is opened.
I didn’t know that either until a co-worker told us a trick to open a new query window pre-filled with some code snippet. To the credit of my co-worker, here it goes about the secrete SQLFile.sql file.
“….earlier today about the upcoming OLAP environment and the need for standardization, and mentioned that I have a template that SSMS automatically launches every time that I open a new tab. The template includes a USE database statement, along with some verbiage for getting a CREATE PROCEDURE or VIEW statement started, and then a comment block, including Change History log, that would be part of the procedure or view.
I have found that it makes it much harder for me to leave out the comments, because I no longer have to open an old stored procedure to find a comment block with which to start.
This script is just something I threw together; please feel free to modify it to suit your needs (like, replacing my name w/yours).
Having said that, I hope we can come up with a standard template for all of us to use, with minimal differences from one developer to another, so that it will help us document our code as consistently as possible.”
To make this template available in SSMS 2008 (or R2), put SQLFile.sql in this folder:
C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\SqlWorkbenchProjectItems\Sql
In SSMS 2012, put SQLFile.sql in this folder:
C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\SqlWorkbenchProjectItems\Sql
Once you put your code snippet in the secrete SQLFile.sql file, your new query window will no longer be empty. It will be pre-filled with your template.
Built-in script templates
SQL Server Management Studio by far is the best tool I’ve ever used. It’s features, such as the Object Explorer, the IntelliSense, the Registered Servers Explorer, and of cause the Template Explorer, are all powerful features that can significantly increase your productivity in SQL development.
In every version I used, SQL Server Management Studio always comes with a bunch of SQL (and MDX) script templates, including templates to create tables, views, stored procedures, triggers, statistics, and functions etc. All you need to do is to open the Template Explorer, by going to View menu > Template Explorer or jus simple using keyboard shortcut CTRL+ALT+T. You’ll see all the built-in templates, .
Create our own custom templates
What I really like about the template feature is that it allows us to create our own custom templates. Although the template feature allows you to auto-create code by filling out the template parameters, I know most SQL developers only use it to store their own code snippets, or team code templates, without bothering with the parameters.
But using the template feature is not without frustration. What frustrates me the most is to figure out where my own custom templates went.
Another mystery – where are my own code templates?
I am running Windows 7, here is the path where Microsoft put all the custom template files.
C:\Users\DefaultUser\AppData\Roaming\Microsoft\SQL Server Management Studio\11.0\Templates\Sql
On Windows 7, the AppData folder is a hidden folder. This certainly added another layer of secrecy to the whole template feature.
On Windows 7, to get the hidden folders to show, enter this command in the command window. This is much faster than opening the Control Panel and looking for the right program wrapped in several folders.
Then choose Show hidden files, folders, and drives in the View tab, as shown in the following screenshot.
Easy to use after all
After all the mysteries are solved, the template feature in SSMS is extremely easy to use and very user friendly.
- Create a folder: this option allows you to create your own folders under the root folder mentioned previously. Create as many folders or sub-folders to better organize your scripts.
- Create Template: this option allows you create your template with your own code.
- Open Template: this option allows you use the pre-filled code in your template.
- Edit Template: this option allows you modify the code in your template.
Many readers like my previous tip of SQL #48 – Block selection with keyboard shortcut in SQL Management Studio (SSMS). So in this blog post, I’ll show you another keyboard shortcut I use in SSMS. This one will increase your productivity in SSMS, and of cause also fun to use.
You are reading some SQL code developed by other developers. The procedure code reads from a dozen SQL tables (or views), and writes to a couple of other tables. You are new to the code, and have tight schedule to modify the code for the new requirements from business.
If you are like me who follows a very consistent code writing style, you want to put each column on a separate line, but you don’t want to do your own typing.
Solution – use Alt + F1 to retrieve a table structure
For the above two scenarios, what you want to do is to list out all the columns in a table, and also all the constraints, identity field and indexes for the tables.
First, double click the table to select it.
Then hold down Alt + F1. This is what you will see.
For the scenario 2, just simply copy all the column names to the query section.
By default, Alt + F1 is equivalent to sp_help. If you have not changed this default setting, you are good to go.
Workaround for tables in schema other than dbo – add square bracket [ ]
If you follow SQL Server’s recommendation of creating schemas for your tables (for security, or for best practice, or whatever reason), the above keyboard shortcut will not work.
I create schemas in my design. I don’t like the workaround, but I have no choice.
First, add square bracket [ ] to the beginning and the end of the table name (incl. the schema name), then double click the table to select it.
Then hold down Alt + F1 again. You will see the same result.
Keyboard shortcut Ctrl + F1 – retrieve procedure code
Last tip is to use Ctrl + F1 to retrieve procedure code. Give it a try!
In a previous blog, SQL #47 – Too many in-line comments totally destroy code readability, I made a point that all SQL developers need to have a consistent writing style to achieve high level of code readability.
Being productive in SQL Management Studio will be your first step to achieve this goal.
In this blog, I’ll show you one trick I use every day in SQL Management Studio. This is one of the many keyboard shortcuts I use to be productive and to achieve the goal of high level of code readability.
I use a lot of tabs/indents in every query I wrote. Hitting the Tab key twenty times for twenty columns can be very tiring (and boring).
Let’s say you want to type a simple SELECT statement with a style as the following. There are two tab spaces after each comma.
You can certainly insert the comma and two Tab spaces for each column, on each line.
I’ll show you a more fun way to do that, and a more productive way.
Block Selection with Keyboard Shortcut Alt + Shift
Yes, block selection is more fun.
Start to insert the comma, and two Tab spaces for the first three lines.
Put your cursor to the beginning line for YearID, before the comma. Then hold down Alt + Shift, and move the right and down arrow key until you have a block selection as shown below.
Block Copy with Keyboard Shortcut Ctrl + C
Let go of the Alt and the Shift key, the block selection will remain. Then press Ctrl + C to copy the block selection.
Now you are ready to insert the block selection anywhere you want.
Block Insert with Keyboard Shortcut Ctrl + V
The keyboard shortcut pair Ctrl + C and Ctrl + V is the only thing I can remember from my early days of programming in C.
Now point your cursor to before column QuarterNumber and press Ctrl + V. You will witness the magic.
Practice the above three Keyboard Shortcuts a couple of times
You are going to a pro!