So you want to see them all?? You asked for it! Here's a list of spotlight features, which can in some ways serve as "hackers documentation," if you search for text of interest. Don't worry: we'll be offering other / better ways to learn about the product's features.
Substitute Parameters in a Query with Minimal Effort
Suppose you have the following SQL:
DECLARE @i int DECLARE @d datetime DECLARE @s varchar(100) SELECT * FROM SomeTable WHERE IntVal = @i AND DateVal >= @d AND StringVal LIKE @sIf you wanted to run the SELECT statement on its own with your own constant values for the parameters @i, @d and @s, one option is to copy-and-paste the statement text and change the parameters to your constants. SQL-Hero offers a way to perform this kind of substitution with very little effort. Hightlight the SELECT statement in the SQL-Hero editor and press F4. A pop-up window appears asking for parameter values for each of the 3 parameters. Once you provide values and press OK, a new editor is opened with the values properly substituted, including the necessary quoting, etc.
Suppress Result Sets in Query Batch
In some cases you may want to run a number of queries that would ordinarily produce a large number of result sets - for example, from a trace workload.
Rather than suffer with a ton of result sets adding to memory overhead (not to mention the CPU power needed to process them), SQL-Hero lets you include the following
XML snippet in your SQL batch (in a comment, for example) to turn off result set processing:
<EDITOR_SUPPRESS_RESULT_SET/>So for example the following SQL when executed in a SQL-Hero editor window would only print the number of rows that would be returned by the SELECT statement:
--<EDITOR_SUPPRESS_RESULT_SET/> SELECT * FROM SomeBigTable PRINT @@ROWCOUNT
Preview Table Data with One Keystroke
Let's say you have the following SQL:
SELECT * FROM Customer c JOIN Order o ON c.CustomerID = o.CustomerID WHERE o.OrderTypeID =Further let's suppose there's an OrderType table that exists and holds only a few static records. In the above query, you'd like to filter it to only select a particular order type, but you've forgotten the ID values for the different order types. If you highlight the text "OrderTypeID" (you can just double-click that text in the editor to do so) and press F11, SQL-Hero brings up a preview of the contents of the OrderType table, in place, without forcing you to navigate to find the OrderType table, or write a quick SELECT to retrieve its contents. The grid that appears will go away when it loses focus, making it easy to continue working. You can also double-click any grid cell to copy the cell text to the clipboard and dismiss the grid in one step. SQL-Hero can infer the table name of interest by comparing the selected text to available table names and will try to find the "best match."
Apply Script to Other Databases with Minimal Effort
You can execute a SQL batch against one or more other databases (versus the database the editor window is associated with) very easily. Use the "Execute In" toolbar button (or "Alt"+"F5") to bring up a window which lets you pick one or more other databases. Any output that would apply to execution in other databases will bring up a new editor window associated to the applicable database. The "last used" database is remembered across invocations of this screen and the "Suggestions" style offers a level of AI to show the most likely targets near the top of the suggestion list.
Copy Column Names with a Shortcut
Imagine you're typing the following SQL manually:
SELECT * FROM BusinessUnit b JOIN Location l ON b.LocationID = l.It's not hard to imagine filling in "LocationID" following the last "." to complete the JOIN, matching foreign key to primary key. SQL-Hero lets you complete this SQL with a short-cut key: "Alt"+"." What's actually happening here is text on the current line that follows the first "." is copied to the current cursor location. Therefore, this would also work:
SELECT * FROM BusinessUnit b JOIN Location l ON b.LocationID = @Where pressing "Alt"+"." fills in the statement with "@LocationID" - a very plausible case where we've passed @LocationID in as a parameter. (Other similar, more complex scenarios are also supported.) We've found this is a big time-saver, even beyond what normal "intellisense" would provide in other products, where we're not interrupted in our flow of writing SQL since we know definitively what we're after: a corresponding field of the same name.
Load Result Sets from a File
You can load tabular content from .CSV (comma separated values), .TSV (tab separated values), .XLS (Excel 2003/2007), and .XML files. HTML files will be supported soon as well. SQL-Hero attempts to perform some cleansing which would allow "slightly formatted" documents to load, meaning for example Excel spreadsheets that have headers and footers will still successfully import as a tabular result set. Once loaded, you can run SQL against loaded result sets using the $$(x) syntax, where "x" in this case can be the result set name or ordinal position (starting with zero).
Query Against Result Sets Using SQL
No matter how you've populated one or more result grids, you can issue queries against them as if they were real tables. Write SQL that includes $$(x) where "x" in this case can be the result set name or ordinal position. For example:
SELECT r.Amount, b.BusinessUnitDesc FROM $$(0) r JOIN BusinessUnit b ON r.BusinessUnitID = b.BusinessUnitIDIn the first result set tab, there would be a grid with columns "Amount" and "BusinessUnitID". We've joined using the BusinessUnitID to get the business unit description. If the "Multiple Results" checkbox is checked, you will get a new result set added to any existing ones which includes "Amount" and "BusinessUnitDesc". If "Multiple Results" is unchecked, the new result effectively replaces all existing ones.
Advanced Content Searching Within SQL Objects
It's relatively easy to do content searching using simple SQL against DMV objects. However, there are drawbacks such as: a) it's only a list that you still need to "hunt and peck" if you want to examine individual objects,
b) you're a bit limited in the kinds of searching you can do. SQL-Hero solves both of these. With content searching, the tree is filtered to only show matched objects. Also, you can use advanced search criteria such as:
a) ww(x) where "x" represents a "whole word" you wish to search for,
b) AND, OR, NOT are all supported to combine search terms,
c) NEAR is supported to filter based on one term being near another term (near in this case is defined as being 40 characters or less),
d) Quotes can be used to delimit "exact match" chunks of text,
e) regex=x where "x" represents a regular expression to be used in matching.
For example, you could use these searches:
ww(businessunitid) near ww(insert) - find objects where the word "businessunitid" occurs near the word "insert" (where for example "inserted" would not match in this case).
"some error message" and not "other error message" - find objects where the exact phrase (not case-sensitive) "some error message" is present but not the phrase "other error message".
regex=\w+\d+\W - find objects where there is one or more "word characters" followed by one or more digits followed by at least one non-word character (e.g. "BusinessUnit1=" would match this).
Advanced Content Searching Against Database Data
Similar to what you can accomplish with content searching against object text, by toggling the "Data Search" button on the toolbar, the same kinds of searches can be issued against data itself. Why could this be useful? Say you're not familiar with a schema but you know some table holds a particular name - go search for it using say "Miller and Joe" and if any row has both these terms, the table will show up in the search result, with scripting done to identify the exact subset of rows containing the data!
Changing Welcome Page Settings
If you've dismissed the Welcome page and set it to "Never" show automatically, you can still bring it up manually. Under the "File" menu option pick "Show Welcome Page".
Revisiting Previously Executed Commands
Have you ever been running various scripts during the day and sometime later you wish you could run something again? Or even just want to know what was run? No worries if you're using SQL-Hero. Toggle on the "Record SQL" button on the toolbar. Now all script execution is recorded. You can revisit previously run commands by going to the "Recorded SQL History" panel. Open that by using the "Manage" menu option and picking "Recorded SQL History". While the "working" icon is spinning on the newly opened editor window, SQL-Hero is bringing back history into the result grid. Double-click a row to open a new window with the executed text.
Running All Existing Unit Tests Directly
There may be time you would like to verify that all unit tests are still executing successfully. Because SQL-Hero internally leverages its own template engine as much as possible, unit testing is really just another kind of template. Select the objects you wish to test, such as by clicking the globe/spyglass icon (Object Locator), check the "Select All" checkbox and click the "Procs" button - all stored procedures will be selected now. Right-click on the object list to get the context menu which should include "Templates for Selected". Pick the "Performance and Unit Test SQL" template. Check the "Select Results" parameter checkbox and click OK on the parameters window. You should now have a script that if executed will exercise the selected objects and report on any failures - they will show in red in the result grid after execution.
Most Recently Accessed Object List
SQL-Hero maintains the list of the last 20 objects you've accessed in the object tree, allowing you to go back and select them again with minimal effort. This feature is available from the toolbar or Alt-F12 in the standalone executable. The default behavior records the object name plus the database it was accessed in, but you can change this to select an object of the same name in the currently selected database, too. The list is ordinarily sorted by most recently accessed at the top of the list, but you can also sort the list by name. Your recently used list is even remembered across sessions!
Change Current Database to Match Editor's
Depending on how you've chosen to set your preferences, the current database object list may be changing as you switch between editors: or you may elect not to do this. If so, you can be working in an editor window and the database object list does not correspond to the database that the editor window is linked to (as showing in the tab header). Rather than force you to hunt for the database, simply right-click in the editor and select "Switch to ..." where "..." will be the database of the editor. That's all it takes!
Using an Existing Object to Create a New One
Imagine you'd like to create a new object that's similar to an existing one. With SQL-Hero, simply open the original object, make your changes, and hit F5 to commit/execute your changes. Even if the object was scripted back as an ALTER - SQL-Hero will automatically translate this into a CREATE of the new object. It even recognizes that you're building a new object and will ask if you wish to delete the old object (effectively renaming it), or keep it. If you keep it, the new object can be automatically selected in the object tree for the current database.
Executing Statements with a Single Keystroke!
A common pattern is you may write a number of SQL statements within the same window, as you research and refine. With many tools, you're expected to highlight the text you want to execute before you hit F5 to actually execute it. The act of selecting text is wasted keystrokes or mouse usage! With SQL-Hero, simply leave your cursor in the immediate vicinity of the statement to execute and press Ctrl-E: your statement is automatically selected and executed.
Open a New Window
Hit Ctrl-B to open a new blank editor window for the currently selected database.
Switch Between Databases with Shortcut Keys
On the Settings tab, you can specify shortcut keys to associate with database connections. (This can be assigned from Ctrl-0 through 9.) In doing so, pressing the shortcut key lets you switch to the desired database very quickly within the editor, in turn potentially opening a new editor window for the database (the actual behavior is something you can set through preferences).
Content Searching - A Smart Twist
You've done a content search over all objects in a database for the word "foo". You might have found an object or two, and after opening one of these, what happens when you press F3 (Find Next)? It locates the first instance of "foo" inside the object!
"Find" - Works with Editor Text and Result Data
If you'd like to search for text in a code editor, Ctrl-F (Find) and F3 (Find Next) are similar to what you get out of other tools. However, the Find window includes a checkbox for "Include data results". When checked, when the last match has been found in the editor window, any available result sets are also searched for text matches. Cells are highlighted if there are matches, and F3 lets you move from match to match in the result grid(s).
Option to Close Windows with Changes Without Prompting
Closing editor windows can be done with a single keystroke: the Esc key. When you do this on a window with changes, the default behavior is to prompt for whether you want to cancel, save, or discard changes. You have some additional choices too: apply your choice (other than cancel) to just this time, every subsequent request during the current session, or every subsequent request, even in future sessions.
The "memory" of your choice can be changed in the Behaviors grid on the Settings tool. ("Ask" implies you should be prompted, with other options implying your preferred default response to the scenario.)
The net effect of electing to discard changes on editor windows with changes means you can close them without any annoying prompts - which can be nice, if slightly dangerous! (We do, however, have a way to recover windows closed by accident, with "Editor List" (Alt-F1), so it's not as dangerous as it sounds.)
Object Tooltips Offer Details
Try getting tooltips on items in the object tree: stored procedures will include "definitions" which can be set via extended properties or user-defined properties (UDP's); tables will include row counts; foreign keys show to/from tables and columns. Even result set grid column headings have tooltips that count distinct values, identify uniqueness, and more. Ultimately this means you can avoid needing to interact more deeply with some objects and get what you need simply by "hovering."
"Close All Others"
Getting many open editor windows can be a pain. If you're certain you only need your current window open, invoking the "Close All Others" tool quickly does just that.
Another way to reduce the clutter of open windows is to use the "Close Unchanged" tool (Ctrl-F7). (If objects are unchanged the assumption is you can locate and open them later, if needed.)
One further extension of this idea is to, on the Settings tool, enable the "automatically close oldest editor window". When checked, this will close the oldest unchanged window when there are more than a specified number that are open, trying to reduce your overall number of open windows. (Yes, we've seen users with 10, 20 or more open in other tools - confusing at best!)
Selecting All Objects (of a Type)
Do you want to select all stored procedures? All views? All tables?
You could do so individually, but this could be tedious. Another way is to use the Locator tool (Shift-F1). With the tool window open, check "select all" and click on the button of the object type you're interested in. Now all of those objects are selected in the tree. What can you do with this state of things? You could drag the selected names to an editor window to get a list, or invoke the context menu against them to perform an action such as setting user-defined properties or running a template against all of them.
Selecting an Object (or Objects) Easily
Selecting objects in the object tree is an important task: simply clicking on a stored procedure name, for example, opens it for editing with no added effort. Therefore, improving the efficiency of selecting objects makes a big difference for productivity. SQL-Hero provides a few ways to do this.
First, in a section of code like this:
SELECT * FROM dbo.MyTableIf you double-click on "MyTable", it will become fully selected in the editor. Now invoke the "Locate Object" tool (F2) - and suddenly the MyTable table has been selected.
Another option is if you've got "MyTable" on the clipboard for some reason - maybe you copied it from an email, or result set cell for example. Simply use the "Locate Object from Clipboard" tool (Alt-F2) and the same behavior happens: the table is selected. Keep in mind, this works for multiple objects as well, if you have a line-delimited list of object names.
Maybe you have "MyTable" selected in the editor but want to select it in a different database. You can use the "Locate (Pick Database)" tool (Ctrl-F2) and get prompted with our intelligent database selector window (more on that in another tip!).
Filtering Result Grids
Grids presenting result sets from queries can be manipulated in numerous ways. One way is to filter based on values.
The grid context menu includes "Filter" (Ctrl-I), which toggles a filter symbol on column headings. Clicking on the filter symbol provides a quick way to filter to specific values, or apply complex filter expressions.
Another option is to use "Filter by Current Cell" - you can elect to filter to a specific value, or exclude a specific value.
Sorting Editor Tabs
We've observed developers using other SQL editors where over time, many windows are opened, and productivity starts to drop as people "hunt" for queries or work they've done recently - and is somewhere in the list of open windows.
SQL-Hero tries to help in multiple ways. One way that's easy to understand is to use the "Sort Editors" tool. When you invoke it, you can pick a desired sort order from a number of predefined options including: by last accessed date, last modified date, created (opened) date. What this means practically is if you were to say sort by modified date, as you work in windows, your most recent work will be kept towards the left of the tab list, while less-recently-worked-on work will "drift" to the right. Suddenly this makes it easier to identify "more relevant" work, with right-side windows being possible candidates to close (or ignore).
Of note, you can also flag windows as being "important" (the big "star" on the toolbar). Important windows can have their own sorting rules, in particular allowing them to stay to the far-left. (I've actually modified objects previously to get the benefits of reordering to the left, where using the "important flag" reduces that need.)
Also of note: you can drag tabs left and right as you wish, but with active editor sorting, your manual moves can be lost as sorting is applied.
Coloring Editor Tabs
Helping pinpoint your work over multiple open editor windows can be a challenge, especially when you have many open windows. One way to help is to use coloring of your editor tabs. You can do this manually using the "Relabel Tab" tool (Ctrl-T): you're prompted for the tab label and a color.
Managing this manually can be a pain, so another option is to automatically color tabs based on the database the tab is associated with. Enable this feature on the Settings tool, checking "auto-color editor tabs not explicitly set". Colors are selected randomly with an effort made to keep them different enough to be visually distinctive among different database aliases used.
Cloning an Editor Window
The simplest way to do this? Ctrl-Q opens a new editor window for the current database, copying all the text out of the current editor window and pasting it into the new editor window.
Comparing Result Grids
With more than one result set grid open over any number of editor windows, you can compare the results using the same preview style as offered for the "Compare Data" tool. (This includes intelligent color coding, showing row-level differences, and column-level differences for modified rows.)
The context menu on result grids includes "Compare Result Grids" which, when invoked, shows a window where you can choose exactly what to compare. The window attempts to identify "comparable" pairs of grids, based on possible natural keys. There are some options to expand the possible detected natural key set, but keep in mind you can only compare results that have shared columns that are unique. The window also lets you select a subset of columns to compare, which could be useful if for example you have an updated date column that's not meaningful for the comparison.
Have a list of objects of interest? Want to create a list of objects that meet some criteria? It's easy to do both using SQL-Hero filters.
The Filter tool (Ctrl-Shift-F) window offers several options that are combined when multiple conditions are used (all must be met). Click on the + button next to any of the 11 different filter criteria to open their details. For example, "Global Include List (Name)" is a basic multi-line text box where you can enter one or more exact object names (can be schema qualified, or not). "Modified Information" is available if you're using history tracking for objects, where you can filter based on "last modification" data that's captured in the SQL-Hero repository. We'll cover the exact meaning of all of these criteria in other documentation.
Clicking on the Expand List toolbar button will change a filter based on say regular expressions into one based on an explicit object name list. This is one way to get a list of objects that meet one of the other 10 filter criteria: copy-and-paste out of the object list after you've coverted to an object list!
SQL-Hero "gets" what might be happening in your current context. For example, say you have an object list filter in place - maybe only a handful of objects are showing in the object tree. If you were to say select an object name and press F2 (Find Object) to try and locate it - but it's not currently visible because of the active filter, you'll be asked whether you want to remove the filter or not. (This is a setting that can be persisted, if you like.) The benefit here is obvious: no need for you to remember clearing filters before trying to do what you really want to do - find a specific object!
What Did I Execute?
Whether you have a script that spans multiple objects or are simply working on a number of individual objects during a work session, it can be helpful to have a record of what objects you "touched." The grid shown at the bottom of the Editor tool offers a way to do this: it accumulates a list of object names, as you execute SQL that creates or alters them.
Try double-clicking on a name in the grid: it'll open the object. Context menu options also let you export the complete list, or select a specific object in a database of choice.
Imagine you've run a query like this:
SELECT [Name], CustomerTypeID FROM Customer
Say your result set looks like this:
+-----------------+-----------+ | Name | AddressID | +-----------------+-----------| | Fred | 29 | | Bill | 46 | +-----------------+-----------+
If you're interested in finding out the address details for Bill, you could re-write your query to join to a presumed Address table, or simply write a query against Address yourself, based on AddressID = 46.
OR - you can use the "Join To" (Ctrl-J) function. Doing so, you'll be prompted "Enter the name of the table to select from where AddressID=46". With the Address table specified, the following script will be generated and executed:
SELECT * FROM Address WHERE AddressID = 46
You can chain this process without writing any SQL yourself!
Result Set Fast Formatting
Getting a result set with a lot of columns can be daunting: finding data can become "hunt-and-peck." SQL-Hero offers an option: grid fast formatting, a context menu option on result grids. This combines multiple actions including: a) sort all column alphabetically, b) hide columns that have singular values, c) resize columns to fit the underlying data.
A specific case we've found this very useful for: say you have two or more records of data that are different only by say a single value out of possibly many columns - and you don't know exactly which column it might be. Fast format will quickly narrow it down since all columns will "disappear" that have the same values, with only the differing values remaining visible.
Get Distinct Column Values
With a result set, you might want to extract values from the grid. One option is to extract all distinct values from a specific column. SQL-Hero has a context menu option to do exactly this, based on your selected cell or clicked column: "Distinct Column Values..." When invoked, a window appears with a multi-line text box, showing all distinct values for that column. You can copy the text at this point with Ctrl-C or clicking the Copy button.
Drag Column Names to Editor Window
It sounds simple - and it is: you can drag result set column names into an editor window.
Select Grid Cells, Copy To Clipboard (SQL IN Format)
Most grids in the app support cell and row selection, with export to the clipboard in multiple possible formats. One such format is "For SQL IN", which lets you take cell values and turn them into a comma-delimited list that's properly "quoted" based on the underlying data type. One can easily do Ctrl-V to paste the string placed on the clipboard, after having typed, for example:
SELECT * FROM Customer WHERE CustomerID IN (
Template: INSERT from Result Grid Rows
One template that's available for result set grids is the "INSERT with VALUES (from Results)". You can find this by right-clicking on any result set grid, going to "Templates" and "Inline T-SQL and DML". When selected, the template parameter screen lets you decide which columns you want to include in the generated INSERT's, what table name to use as the target of the INSERT's, etc. After saying "OK", your INSERT statements will appear in the current editor window, corresponding to all rows in the result grid, or if you selected specific rows, filtered to that subset of data.
Template: Bulk INSERT to Table From Result Set Data
If you have an open result set grid, you can get its data into a table very easily. Right-click on the result grid, select "Templates", and "Inline T-SQL and DML". The "Bulk Load Table from Result Set" template can be selected, with parameters that let you provide a target table name. If you check "Truncate/Create", the target table will be effectively dropped if it exists and re-created based on the shape of the result set. After continuing, the result set data will appear in the table you specified - quickly - given the fact BULK INSERT is used behind the scenes.
Detection of Live Changes Made by Others
Say you're working on stored procedure "foo" - it's open in an editor window. If someone else makes a change in "foo" and commits their change, if you switch to your window with "foo" in it, SQL-Hero will warn you that the object has changed in the database since you opened your copy of "foo". You can choose to reload "foo" at this time or contine with your copy of it.
Live Detection of Possible Model Drift
Say you have a stored procedure "foo" that exists in a development and staging database. Also suppose that someone has made a change in "foo" - in staging, without applying the change in development. Of course, this is not ideal, but if it does happen, it's important to identify such changes otherwise it becomes easy to lose them if say changes are made in "foo", assuming the development copy is the latest, and then later moved to staging.
To help address this, SQL-Hero leverages its history data (if you have it enabled) so that when you open "foo" in development, a pop-up window appears advising that there could be an issue with "foo" versioning between development and staging.
Color Result Grid Rows by Column Value
A context menu option available on result set grids is "Grid" - "Color by Column". Pick a column name and the grid will be colored based on values for that column. Coloring it randomized, where an attempt is made to make colors distinctive. That said: if you don't like the coloring offered, you can retry the operation and a new set of colors will be used. This can be an effective way to quickly call out different values - and the coloring even carries over if you export the grid to Excel.
Color Object Names Based on Criteria
The "Highlight Objects" (Shift-F10) tool lets you provide one or more rules for coloring of object names in the object tree. You can color based on name (regular expressions - either include or exclude), object type, or user-defined properties. User-defined properties (aka UDP's) are something that either map to database extended properties or can be stored in the SQL-Hero global database that you're able to install using the SQL-Hero installer program. UDP's are an important way to associate typed values with objects of all types, in turn being very useful for code generation scenarios.
Parse and Paste - Example #1
The "Parse and Paste" tool in SQL-Hero lets you take data that's sitting on the clipboard and write complex transformations for it, with the result being pasted into your active editor window. The use cases here are numerous, so let's focus on capability and let your imagination take over!
Say you have a list of 20 stored procedure names that you've placed on the clipboard. You'd like to execute all 20 of these procedures, with a specific set of parameters. Invoking "Parse and Paste" brings up a window where you can specify a) how to interpret the clipboard data (delimiters, etc.), b) how to transform it. In this case, if you typed this into the multi-line text box:
EXEC <!row/> 'parm1', 'parm2';
... you might see something like this in the preview area:
EXEC MyProc1 'parm1', 'parm2'; EXEC MyProc2 'parm1', 'parm2'; EXEC MyProc3 'parm1', 'parm2';
If this were the case, we could infer you had the following data on the clipboard, since <!row/> is markup that's substituted for each row of data on the clipboard (the line delimiter being the default - but can be changed):
MyProc1 MyProc2 MyProc3
Selecting "OK" would insert your EXECUTE statements into the current editor window, ready for execution. This is just one simple example of what can be done: much more is possible, including field-level manipulation, INSERT statement inference, and more!