SQL Smack down is back!
It's been a while since the last installment, but it took me a while to think of some more topics. In this edition we will go over a few Query Analyzer tips, so without further ado, here we go.
In the past I have pushed many of you to use the Query Analyzer tool more and more, so I thought I would share a few tips to make life easier.
I know I have referenced this many times in the past, but this is likely the most useful tool in all of SQL Server. To access Books Online, choose Transact-SQL Help from the Help menu, or use the keyboard shortcut of Shift+F1. The index is extremely handy, as you can look up just about anything you need to know about T-SQL.
The Object Browser is similar in many ways to the Enterprise Manager's functionality. To display the Object Browser, look in the Tools | Object Browser menu, or use the shortcut key of F8.
The first item you will see at the top of the Object Browser pane is a combo box for selecting which server to work with. By default this is the server you are currently connected to. The next item is a list of databases and common objects. The common objects basically just give you a quick reference for many of the built in functions, data types, and objects. The database section is the part we are most interested in.
If you expand one of the databases by clicking the + icon next to the database name you will see a list containing User Tables, System Tables, Views, Stored Procedures, Functions, and User Defined Data Types. Expanding User Tables, you will see a list of all tables you have created in the database. You can script any of these tables by right clicking the object, then choosing the appropriate action from the scripting section of the menu. In the Stored Procedures list, you can script individual stored procedures for editing, and the same applies to the other items listed under databases.
Take some time to look through the Object Browser. It has many useful features that you currently use through Enterprise Manager, and you don't have to switch between applications.
Query Shortcuts are basically just user defined shortcut keys that run T-SQL statements. If you choose Customize from the Tools menu, you will be prompted with a form that contains a list of shortcut keys. By clicking in the Stored Procedure box next to the shortcut of your choice, you can enter in just about any T-SQL command. When you apply the changes, you can execute the command in Query Analyzer by using the keyboard shortcut.
In the Options from the Tools menu, there are two main settings that may be of use to you. The first is the Query File Directory setting in the General tab. If you have a common directory location that you store your SQL files in, change this option and the open file dialog will always default to that folder. The second setting is the Maximum Characters Per Column in the Results tab. If you have ever wanted to see more characters in the results grid than allowed by default, this is the setting to change. I usually turn this number up from the default of 256. The maximum value allowed is 8192, but it uses more memory the higher you set the option. Also, if you use text results option, you can get some interesting results when you have several longer columns.
For this tip I will just list a few of the more helpful keyboard shortcuts that I use.
| Shortcut | Action |
|---|---|
| F5 | Execute Query |
| F6 | Cycle Panes |
| F8 | Show/Hide Object Browser |
| Ctl+T | Show Results in Text |
| Ctl+D | Show Results in Data Grid |
| Ctl+R | Show/Hide Results Page |
| Shift+F1 | Show Books Online |
| Ctl+Tab | Cycle Query Windows |
Most of the standard Windows shortcuts work the same also, including the copy/cut/paste and new/open/save file shortcuts. The only real difference you may notice is that if you are used to using Ctl+R to replace, it is Ctl+H is the Query Analyzer.
Well that's it for the edition of SQL Smack down. As always, if you have any questions about these or any other topics, please talk to Barry.