![]() | |
All SQL Workbench/J specific command can only be used from within SQL Workbench/J |
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:
Parameter | Description |
---|---|
-searchValues |
A comma separated list of values to be searched for. |
-useRegex |
Valid values are
If this parameter is set to true, the values specified with
The default for this parameter is |
-matchAll |
Valid values are
This specifies if all values specified with
The default for this parameter is |
-ignoreCase |
Valid values are When set to true, the comparison is be done case-insesitive ("ARTHUR" will match "Arthur" or "arthur").
The default for this parameter is |
-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
When specifying a type that contains a space, the type name neeeds to be
enclosed in quotes, e.g.
The default for this parameter is This parameter supports auto-completion. |
-objects |
A list of object names to be searched. These names may contain SQL wildcards, e.g. 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
→
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.
![]() | |
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:
Parameter | Description |
---|---|
-searchValue |
The value to be searched for
This parameter is ignored when using |
-ignoreCase |
Valid values are When set to true, the comparison is be done case-insensitive ("ARTHUR" will match "Arthur" or "arthur").
The default for this parameter is |
-compareType |
Valid values are
When specifying
The default for this parameter is |
-tables |
A list of table names to be searched. These names may contain
SQL wildcards, e.g. This parameter supports auto-completion. |
-types |
By default 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.
|
-columns |
By default |
-retrieveCLOB |
By default 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 |
-retrieveBLOB |
By default
If |
-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.
|
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.
The following statement will search for the text foobar
in all columns
and all tables.
WbGrepData -searchValue=foobar -tables=*
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=*
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=*
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
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