Selecting tables with the wildcard selector v7

Suggest edits

When selecting tables for creating a publication for a single-master replication system (see Adding a publication) or a multi-master replication system (see Adding a publication), there might be cases when the number of available tables for selection is so large that selecting the ones you want is difficult and time consuming.

You might also encounter this issue when adding tables to an existing publication (see Adding tables to a publication) or deleting tables from an existing publication (see Removing tables from a publication).

In such cases, the wildcard selector lets you choose a set of tables by using pattern matching similar to the technique used by the SQL statement LIKE clause.

Wildcard selector patterns

Pattern matching, as performed by the wildcard selector, is the process in which the eligible tables for an operation are returned in a filtered list if their schema and table name combination match a character string, called a pattern. Matching a pattern means that the schema and table name combined in a string formatted as schema_name.table_name matches the pattern, character by character, according to the rules designated for the characters appearing in the pattern.

If the schema_name.table_name string matches the pattern, then the schema and table are displayed in the filtered list for that pattern, which is the Available Tables field of the Wildcard Selector dialog box. You can then selectively choose the tables from the filtered list to add to a local list, which contains the candidate tables for the operation for which you're using the wildcard selector.

Similarly, you can remove tables from the local list that were previously selected if you decide that you don't want those tables applied for the operation.

With the exception of wildcards characters, characters appearing in a pattern require that the character in the corresponding position in the schema_name.table_name string must match the pattern character. This pattern matching is not case sensitive.

Wildcards characters are interpreted as follows:

? – Single-character wildcard specifies that any single character can exist in its position of the pattern. (The SQL LIKE clause uses the underscore character (_) for this purpose.)
% - Multi-character wildcard specifies that any combination of multiple characters, including the absence of any character, can exist in its position of the pattern.
[abc...] – List wildcard specifies that any one of the characters listed in the brackets can exist in its position of the pattern.
[a-d] – Range wildcard specifies that any one character that is greater than or equal to the character preceding the hyphen (-) and less than or equal to the character following the hyphen can exist in its position of the pattern.
[abcd-f...] – List and range combination wildcard specifies that any character that matches any of the list or range wildcard descriptions can exist in its position of the pattern.
Any character specified in the pattern other than ?, %, [, ], and the characters enclosed in the square brackets of a list or range wildcard must exist in its position of the pattern. Pattern matching of such characters is not case sensitive.
NOT pattern, !pattern, ! pattern – Exclusive pattern specifies that tables that match the pattern string indicated by pattern are omitted from the filtered list. Tables that don't match pattern are included in the filtered list. The keyword NOT can be in upper case, lower case, or mixed case, but it must be followed by a single space character preceding pattern. !pattern specifies that pattern immediately follows the exclamation point (!) with no intervening space character. ! pattern specifies that a single space character exists between pattern and the exclamation point (!).
pattern* - Specify the asterisk (*) immediately following the pattern with no intervening space character if you want to include tables in the filtered list that match pattern and were previously selected (that is, the local list tables) along with tables that weren't selected. In the filtered list, each previously selected, local list table is displayed with its check box selected. Each filtered list table that wasn't previously selected has its check box cleared. By default, when you omit the asterisk, only tables that were not previously selected are returned in the filtered list. Using the asterisk is useful for removing currently selected tables from the local list.

You can see the wildcard pattern definitions and examples from a help screen. Right-click the Filter Pattern text field of the Wildcard Selector dialog box to access the help screen.

Using the wildcard selector

The following terms are used in the Wildcard Selector dialog box and the description of the wildcard selector feature:

Calling dialog box. This is the dialog box of the operation from which you invoke the Wildcard Selector dialog box. The final set of tables from the wildcard selector is applied to the operation managed by the calling dialog box.
Table list. This is the list of currently selected tables displayed in the calling dialog box. Each selected table has its check box selected.
Local list. This is a temporary, internal copy of the table list managed by the wildcard selector. The wildcard selector allows you to add tables to the local list and to remove tables from the local list. When you select Done in the Wildcard Selector dialog box, the local list becomes the table list. In other words, the local list tables appear as the selected tables of the calling dialog box.
Unselected tables. These are the tables eligible, but not selected, for the operation with which you're using the wildcard selector. When you select Filter List, the unselected tables that match the filter pattern are listed in the Available Tables field of the Wildcard Selector dialog box. To list all unselected tables, use the percent sign (%) for the filter pattern.
Selected tables. These are the tables you selected for the operation with which you're using the wildcard selector. That is, these tables make up the local list. To display selected tables that match a filter pattern, add the asterisk character (*) immediately after the filter pattern. Each selected table has its check box selected.

To use the wildcard selector:

Prior to opening the Wildcard Selector dialog box, you can start selecting tables from the list of available tables of the calling dialog box.
From the calling dialog box, select Use Wildcard Selection.
In the Wildcard Selector dialog box, any tables that you preselected are included in the local list used by the wildcard selector to manage the addition or removal of tables. When you first open the Wildcard Selector, the default filter pattern is the percent sign (%), which returns all eligible, unselected tables.
The Available Tables field displays the filtered list matching the pattern used in the Filter Pattern text field.
Enter a pattern in the Filter Pattern text field to narrow your desired table selection. Select Filter List to display the tables that match the pattern.
Select tables from the Available Tables list that you want to add to the local list by selecting each table’s check box. Select Select All check box to select all tables and then deselect certain tables by clearing the check box.
Select Apply Selections to Local List button to add the selected tables to the local list.
Apply as many filter patterns as needed to add all of your desired tables to the local list.
Select all the tables from this filtered list by selecting Select All.
Click Apply Selections to Local List to add all tables to the local list. After applying the selections, there are no unselected tables remaining that match the filter pattern.
By using the asterisk after the pattern, you can display previously selected tables comprising the local list. Each selected table has a check mark its check box.
You can remove selected tables from the local list by clearing each such table’s check box.
Removal of the deselected tables from the local list occurs along with the addition of any newly selected tables when you select Apply Selections to Local List.
The deselected tables still appear in the Available Tables list since they still match the pattern but as unselected tables.
When the local list contains all of the tables you want, select Done. The local list becomes the list of selected tables displayed in the calling dialog box.
When the calling dialog box contains the complete list of your desired tables, select the appropriate action in the calling dialog box to complete the operation with the selected tables.

← Prev

Common operations

Creating a schedule

Selecting tables with the wildcard selector v7

Wildcard selector patterns

Using the wildcard selector

← Prev

Next →