24. Search data and code in the database

24.1. Search source of database objects - WbGrepSource
24.2. Search data in multiple tables - WbGrepData
[Important]

All SQL Workbench/J specific command can only be used from within SQL Workbench/J

24.1. Search source of database objects - WbGrepSource

The command WbGrepSource can be used to search in the source code of the specified database objects.

The command basically retrieves the source code for all selected objects and does a simple search on that source code. The source code that is searched is identical to the source code that is displayed in the "Source" tab in the various DbExplorer panels.

The search values can be regular expressions. When searching the source code the specified expression must be found somewhere in the source. The regex is not used to match the entire source.

The command supports the following parameters:

ParameterDescription
-searchValues

A comma separated list of values to be searched for.

-useRegex

Valid values are true, false.

If this parameter is set to true, the values specified with -searchValues are treated as regular expression

The default for this parameter is false.

-matchAll

Valid values are true, false.

This specifies if all values specified with -searchValues have to match or only one.

The default for this parameter is false.

-ignoreCase

Valid values are true, false.

When set to true, the comparison is be done case-insesitive ("ARTHUR" will match "Arthur" or "arthur").

The default for this parameter is true.

-types

Specifies if the object types to be searched. The values for this parameter are the same as in the "Type" drop down of DbExplorer's table list. Additionally the types function, procedure and trigger are supported.

When specifying a type that contains a space, the type name neeeds to be enclosed in quotes, e.g. -types="materialized view". When specifying multiple types, the whole argument needs to be enclosed in quotes: -types='table, materialized view'

The default for this parameter is view, procedure, function, trigger, materialized view. To search in all available object types, use -types=*.

This parameter supports auto-completion.

-objects

A list of object names to be searched. These names may contain SQL wildcards, e.g. -objects=PER%,NO%

This parameter supports auto-completion.

-schemas

Specifies a list of schemas to be searched (for DBMS that support schemas). If this parameter is not specified the current schema is searched.

This parameter supports auto-completion.

The functionality of the WbGrepSource command is also available through a GUI at ToolsSearch in object source

24.2. Search data in multiple tables - WbGrepData

The command WbGrepData can be used to search for occurrences of a certain value in all columns of multiple tables. It is the command line version of the (client side) Search Table Data tab in the DbExplorer. A more detailed description on how the searching is performed is available that chapter.

[Important]
To search the data of a table a SELECT * FROM the_table is executed and processed on a row-by-row basis. Although SQL Workbench/J only keeps one row at a time in memory it is possible that the JDBC drivers caches the full result set in memory. Please see the chapter Common problems for your DBMS to check if the JDBC driver you are using caches result sets.

The command supports the following parameters:

ParameterDescription
-searchValue

The value to be searched for

This parameter is ignored when using isNull for the compare type.

-ignoreCase

Valid values are true, false.

When set to true, the comparison is be done case-insensitive ("ARTHUR" will match "Arthur" or "arthur").

The default for this parameter is true.

-compareType

Valid values are contains, equals, matches, startsWith, isNull

When specifying matches, the search value is used as a regular expression. A column is included in the search result if the regular expression is contained in the column value (not when the column value completely matches the regular expression entirely).

The default for this parameter is contains.

-tables

A list of table names to be searched. These names may contain SQL wildcards, e.g. -tables=PER%,NO%. If you want to search in different schemas, you need to prefix the table names, e.g. -tables=schema1.p%,schema2.n%.

This parameter supports auto-completion.

-types

By default WbGrepData will search all tables and views (including materialized views). If you want to search only one of those types, this can be specified with the -types parameter. Using -types=table will only search table data and skip views in the database.

This parameter supports auto-completion.

-excludeTables

A list of table names to be excluded from the search. If e.g. the wildcard for -tables would select too many tables, you can exclude individual tables with this parameter. The parameter values may include SQL wildcards.

-tables=p% -excludeTables=product_details,product_images would process all tables starting with P but not the product_detail and the product_images tables.

-columns

By default WbGrepData will search all columns of a table (or view). If you want to limit the search to some columns only, you can define a list of column names to be included. Wildcards are not supported.

-retrieveCLOB

By default CLOB columns will be retrieved and searched. If this parameter is set to false, CLOB columns will not be retrieved.

If the search value is not expected in columns of that type, excluding them from the search will speed up data retrieval (and thus the searching).

Only columns reported as CLOB by the JDBC driver will be excluded. If the driver reports a column as VARCHAR this parameter will not exclude that column.

-retrieveBLOB

By default BLOB columns will not be retrieved for those rows that match the criteria to avoid excessive memory usage.

If BLOB columns should be retrieved, this parameter needs to be set to true. Enabling this will not search inside the binary data. If BLOB columns should be searched (and treated as character data), use the -treatBlobAs parameter

-treatBlobAs

If this parameter specifies a valid encoding, binary (aka "BLOB") columns will be retrieved and converted to a character value using the specified encoding. That character value is then searched.

-treatBlobAs="UTF-8" would convert all BLOB columns in all tables that are searched to a character value using UTF-8 as the encoding. Therefore using this option usually only makes sense if a single table is searched.

24.2.1. Examples

24.2.1.1. Search for a specific value in a single table

The following statement will search for the text Arthur in all columns and all rows of the table person. It will find values foobar, somefoo or notfoobar:

WbGrepData -searchValue=foo -tables=person -ignoreCase=true

-ignoreCase=true is the default behavior and can be omitted.

24.2.1.2. Search for a specific value in all tables

The following statement will search for the text foobar in all columns and all tables.

WbGrepData -searchValue=foobar -tables=*
24.2.1.3. Search for a specific value at the beginning of a column value

The following statement will search for the text foo in all columns and all tables. It will match the value foobar, but not barfoo

WbGrepData -searchValue=foo -compareType=startsWith -tables=*
24.2.1.4. Search for a specific value with an exact match

The following statement will search for the text foo in all columns and all tables. It will only match the value foo or FOO but not somefoobar

WbGrepData -searchValue=foo -compareType=equals -tables=*
24.2.1.5. Search for patterns

The following statement will search for any value where three characters are followed by two numbers. It will match foo42, bar12

WbGrepData -searchValue="[a-z]{2}[0-9]{2}" -compareType=contains -tables=person

As the column values are only tested if the regular expression is contained, not if it is an exact match. The above search will also return foo999.

To get an exact match using the contains type, the regular expression needs to be anchored at the start and the end. The following will only find only values that start with (exactly) two characters and are followed by (exactly) two digits.

WbGrepData -searchValue="^[a-z]{2}[0-9]{2}$" -compareType=contains -tables=person
24.2.1.6. Search for multiple values

The following statement will return rows where any column either contains the value foo or the value bar:

WbGrepData -searchValue="foo|bar" -compareType=contains -tables=person

As the column values are only tested if the regular expression is contained, not if it is an exact match. The above search will also return foo999.

For more information about regular expressions please visit: Regular-Expressions.info