Toad Data Point 5.4 - User Guide

Filter Databases and Objects

Toad allows you to filter the list of databases or objects that display in the Object Explorer. You can use filters to reduce the number of databases and/or objects to work with.

Types of Filters

You can create a Quick filter by entering a search string in the object filter field. You can also use the Filter dialog (click ) to choose additional filter options. If you use the Filter dialog, you can also name and save your filter.

Note: If you rename a table that has a filter defined, it results in an orphaned filter. Orphaned filters are automatically removed unless you used a tool other than Toad to remove them.

Before Creating a Filter

A filter is associated with a specific list of objects from a specific database. Consider the following guidelines when setting filters:

• A filter applied to a set of objects such as tables in one specific database connection is not automatically applied to the list of objects in another database connection.

• A filter applied to a list of tables on one schema in the database is not automatically applied to the list of tables for a different schema in the same database.

• A filter set for a specific schema or object is applied when altering objects, using the SQL Editor, Master Detail Browser, etc., for that schema or object.

  Tip: To apply any currently-set filters to the code completion list in the editor, select the Apply explorer filters option in Tools | Options | Editor | Code Completion.

Create Filters

To create a Quick filter (objects only)

• Enter a search string to use for filtering in the object filter field (below the database field) in the Object Explorer.

  Note: As you enter a search string, the currently specified Search Condition displays in a blue banner below the object filter field. This Search Condition will be used in your filter. To change the Search Condition, click and select a different one from the Search Condition drop-down.

  

To create a Detailed filter

1. Click beside the database or object filter fields in the Object Explorer.

2. Specify criteria for the filter in the Properties tab. Review the following for additional information:

   Search String	Enter a text string and search characters to use for filtering.
   Search Condition	Select a search type to use in this filter. Note: You can set a default value for this field in Tools | Options | Explorer | General. If you don't specify a default value in Options, this field defaults to the search type you specified the last time you created or modified a filter (includes previous sessions). Tip: To include in your text string certain characters normally used in search logic, such as the underscore (_), use one of the following Search Conditions: Contains (Exact) Starts With (Exact) End With (Exact)
   Case Sensitive	In DB2 connections, this property is set to True by default.

3. To create custom SQL, select the SQL tab and then select the Enable custom SQL option. Modify the existing SQL. (The existing SQL reflects the criteria you specified in the Properties tab).
4. Select the Preview tab to review the list of databases or objects that are selected based on your filter criteria.

To create a Named filter

1. Click and create a Detailed filter (see above).
2. Enter a name for the filter and click . Creating a name for the filter allows you to use it on other database connections.

Creating a new Quick filter from a current filter

• If you apply a filter and then enter a search string into the object filter field without completely clearing the current filter in the field, the new search string is applied, as well as the properties specified for the old filter.

• If you apply a filter and then completely clear the object filter field before entering a new search string, all properties return to the default values.

  Note: The Search Condition also returns to the default value. This default is either the Search Condition you specified the last time you created or modified a filter or it is the new default you specified in Options.

Use Filters

To use Named filters

• To apply a Named filter, click the down-arrow beside and select the filter from the drop-down list.

• To clear a Named filter, click the down-arrow beside and select <None>.

  

• To determine if a filter is applied, observe the filter icon.

  Icon	Indicates
  	No filter is applied (all databases/objects display).
  	A filter is applied.

Import/Export Filters

You can export all named filters and you can export all filters defined for the current connection.

• To export Named filters or all filters defined for the current connection, click the down-arrow beside in the database field and select Export Filters.

  

• To import filters previously exported from Toad, click the down-arrow beside in the database field and select Import Filters. Browse to and select the filter file (.tfp) to import.

Tip: Click in the Database Explorer (Viewer) toolbar to clear all database, object, and data filters.

Related Topics

Database Explorer

Object Explorer

Understand a Table Using the Database Explorer

Click here to view a video of this feature.

Note: This topic focuses on information that may be unfamiliar to you. It does not include all step and field descriptions.

To understand a table using the Database Explorer

1. Connect to the Toad Sample (Access) database in the Navigation Manager.

2. Right-click the CONTACT table and select View Details.
3. View information for the following:
   1. Click the Indexes tab and view the index information.
   2. Click the Primary Keys and Foreign Keys tabs to view relationships.
4. Click the Data tab.

5. Place the cursor over the LAST_NAME column, click , and select Acton.

   

   The only row that now displays is the one with Acton as the last name.

6. Click to the right of the LAST_NAME column again and select All to clear the filter.
7. Click the CONTACT_ID column to sort the rows in ascending or descending order.

8. Click in the top left of the Data tab.

   

9. Create the following filter in the Where Clause tab:

   CONTACT_ID > 2000

   The filter string displays at the bottom on the grid. Click to remove the filter.

10. Select a row of data in the grid. Notice that the row number changes in the bottom right of the status bar:

    

11. Under the SEX column, right-click the area below the last row in the grid that says "Right-click to add column totals" and select Count. The total number of rows that have either a M or F value display.
12. Click to the right of the SEX column and select F. The count value updates based on the new filter.
13. Click to the right of the SEX column again and select All to clear the filter.

14. Select the SEX column header and drag it the area above the columns that says "Drag a column header here to group by that column":

    

    You can expand each group to view the row data.

15. Switch the default view:
    1. Right-click within a group or row on the grid and select Show | Card View.
    2. Scroll through some records.
    3. Click Customize in the Card View window.
    4. Click Filter beside the ADDRESS_ID column and select 100. Notice how the card view is filtered, but not the grid view.
    5. Close the Card View window.
16. Edit a row:
    1. Select a row in the FIRST_NAME column and edit the value.
    2. Press ENTER to update the row.

    3. Go to the BIRTH_DATE column and use the date picker to change the date.

       Each data type have an appropriate editor.

       Note: If you are using Oracle or a database that does not have auto commit on, you will need to press Commit at the left hand bottom of the window.

17. Update the number of rows in in the data:
    1. Select Tools | Options | Database | General from the menu.
    2. Enter 50 in the Number of rows to initially fetch in data tab.
    3. Click OK.

    4. Press F5 in the data grid. Notice the bottom right status bar now says "1 of 50 (scroll for more)".

       The option you set controls the number of rows fetched in the Data tab.

    5. Click the scrollbar and hold down to retrieve more.
    6. Right-click the grid and select Read All Rows.

Related Topics

Database Explorer

Search for Objects

You can quickly locate an object rather than browsing through a list of objects in the Database Explorer orObject Explorer. Object Search locates the following:

• Names of objects for a string.

• Text in objects that include source code, which typically includes procedures, functions, triggers, data types, and views. However, the database provider determines which objects include source code.

• Variable names and comments in the object's source code.

• Column names for tables and views.

To search for objects

1. Click on the toolbar (F4).  

2. Specify search criteria and click Search.

   Note: To append additional search results to the current search, click Append instead. If you click Search, new results replace the previous search results in the data grid.
   

Tips:

• To remove objects from the search results, right-click the objects in the data grid and select Remove Items. This is useful if you want to narrow the list of results to relevant objects.This does not remove the objects from your database.

• To create a DDL script from the search results, right-click the data grid and select Create DDL Script. You can then paste the results into the Editor.

• To perform any actions available for the object, such as creating, altering, dropping, exporting, and generating reports or SQL, right-click the results in the data grid and select an option.  

Create an Database Diagram to Use as a Query Template

To create a Database Diagram to use as a query template

1. Connect to the Toad Sample (Access) database in the Navigation Manager.

2. Select Tools | Diagram.

3. Drag the CONTACT table in the Object Explorer to the Diagram pane.

   

4. Right click the Diagram pane and clear Show Dependencies to hide the Referenced Objects at the bottom of each table/view, as illustrated in the following screen capture.

   

5. Right-click the ORDERS table and select Notes.

6. Enter the following note in the window for other users:

   The shipping and billing ADDRESS_ID foreign key relations are not needed in most queries and should be hidden when building a query.

7. Because it is against company policy to display sex and a home phone number in reports, right click on each of these columns in the CONTACT table and select Hide Column.

   Tip: If you have a large number of columns you need to hide, you can right-click a table and select Manage Hidden Columns to quickly select them from a list.

8. Click Add to Project from the wizard bar at the bottom of the window and enter DBDiagram_Test asthe name for the file. This adds the file to the current project in the Project Manager under a Diagrams folder.
9. Close the Database Diagram window and open the Project Manager window.
10. Expand the Diagrams folder and double-click DBDiagram_Test to reload the file in a new Database Diagram window.
11. To share your project, hidden columns, and notes with colleagues, complete the following:
    1. Select Tools | Options | Environment | Network Share, and navigate to a shared drive where you want to store the Project Manager file.
    2. Select Database | Cache from the open Options window and set the following options:

       Object An notation Cache	Description
       Share object annotations	Select this checkbox and enter a shared drive location where users can access this file.
       Share hidden column settings	Select this checkbox.

    3. Provide the location of the Network Share and Object Annotation Cache settings to colleagues who need to use this diagram.

Click here to view a video of this feature.

Related Topics

Diagram Relationships