The Column Finder tool
Published 06 January 2020
The Column Finder dialog box is used to find tables with columns of a given name or to compare columns in one table with columns in other tables. Once found, the candidate tables can be incorporated into masking rules.
The Column Finder dialog box is very useful when the situation arises where candidate tables and columns for masking need to be discovered. The preferred method of identifying potential target tables is to have extensive knowledge of the structure of the tables and the relationships between the tables. However, for those situations where the knowledge of the table structure and relationships is, unavailable the Column Finder dialog box is there to help with the discovery process.
The Column Finder dialog box identifies other tables by searching through their column information using criteria such as: having the columns similar to a text string, being of same name, having the same type or having the same size.
The Data Masker Column Finder Dialog Box
There are two ways to search for columns using the Column Finder dialog box. A search can be performed looking for a match to a specific column from a selected source table using the top central area. The bottom Find area will search for an arbitrary string of text. Note: when searching for a text string it is not necessary to select a source table or column..
Text String Searches
When performing a text string search, enter the text you wish to find in the matching column names and press the bottom Find button. By default, the search is performed as a simple exact string check - any column equal to the the matching text will be identified. All text searches are case insensitive. Selecting the Regex Match Mode allows the search to be performed using sophisticated Regex search strings. In Wildcard search mode a % character signals a multi-character wildcard search and the _ character can be used to specify a single character. Use the % or _ wildcard characters in combination at the start, end or middle of the text for sophisticated searching:
Trailing % (i.e. XYZ%)
This type of query would find tables which contained columns such as XYZ and XYZABC but not those with columns of the form ABCXYZ.
Leading % (i.e. %XYZ)
This type of query would find tables which contained columns such as XYZ and ABCXYZ but not those with columns of the form XYZABC.
Leading and Trailing % (i.e. %XYZ%)
This is equivalent to the default mode of searching and such a query would find tables which contained columns such as XYZ, ABCXYZ and XYZABC.
Similar Column Searches
The panel on the left side of the dialog box lists all of the tables in the schemas that the Data Masker application currently knows about. It is in this area that the source table for a similar column search is selected. If the table required is not present in the list, then probably the schema will need to be refreshed. Use the Refresh Tables and Indexes button on the Rule Controller to reload this information.
Once a source table is selected, the columns it contains will appear in the panel at the top right of the dialog box. When one of the source table columns is selected, use the mouse to check the desired options within the search area and click on the top Find button. The tables that have columns matching the search criteria will appear in the target table and column panel on the right. This panel includes the table name with the matching columns listed in boldface type. An optional additional source row count column for each table can be enabled by clicking on the options button. The Show All Columns check box at the bottom of the right hand results panel can turn on or off the visibility of the other columns in the result tables (the columns other than those that match the query). This is sometimes useful when there are large numbers of matching items.
Making a Rule from Search Results
Rules can be created by right-clicking tables or columns on either the left or right hand pane and choosing from the displayed context menu.
Selecting the Make Rule button will pop-up selector for rule type and by continuing you can create a rule in the selected controller. Once the rule type has been selected, the new rule form will start with as much of the table and column information that is known already filled in.