DBTextFinder, an application to find texts in databases

The catalog views contain all the information concerning the structure of the database: schemas, tables, table fields, data types of the fields, integrity constraints, stored procedures code, etc. The knowing of them is essential to develop tools for data management independent of the structure. In this article we will view an example of one of these tools, DBTextFinder, a simple application that will allow us to search for text in the records of our database and within the code of the views and stored procedures.

This application works with any database, without more than create a library with a standard interface and register it in the program. I have developed three examples to access SQL Server, MySQL and Oracle 12c databases, in this link you can download the DBTextFinder connectors sample code.

In this another link you can see how to create a simple WCF connector for DBTextFinder.

The main usefulness of the application is to search text in a database, but also provides the option of replacement and delete records. Use these options with caution, and never without having ensured well first that they do correctly what you expect of them.

In this link you can download the DBTextFinder setup program. Then we will review how to set up and use it.

Configure the database connections

The first thing to do is set up a connection to a database. To do this, we will use the Manage Connections option in the Options menu:

When you open the Connector drop-down list you can view the different connectors that are registered in the application. To create a new connection, select the appropriate connector for the database and the <New> option in the drop-down list there on the right. In this drop-down also appears the list of connections that you have already set up, and you can modify their parameters by selecting them.

In the Connection Name text box you have to write the identifier you want to give to the connection, and then provide the parameters of the connection, in the Server text box, write the IP or the server name, or, in the case of Oracle, the TNS service name. In the Catalog text box, type the name of the database you want to access (with the Oracle connector it is not necessary, since it is defined in the TNSNAMES.ORA file), and finally, the User Name and Password or select Windows Authentication, if available.

Finally, you can test the connection with the Test Connection button, and save it with the Save button.

Register new connectors

With the Install Connector option, in the Options menu, you can register new connection libraries to other databases. Simply browse and select the file with the library, which will be recorded in the appropriate section of the application config file.

You can activate or deactivate connectors with the Manage Connectors option in the Options menu:

Only the marked connectors will be available.

Searching for texts

Once configured the access to the database, use the New option in the File menu to access the search interface.

In the Connection Name dropdown list select the connection to the database where you want to search. In the right side are listed the available objects, and you can select those in which you want to search. You can select the options Search in Tables, to display the tables, and Search in Procedures, to show the list of procedures and views.

In the Search Expression text box write the text string to search. You can also use regular expressions to search. There is the Ignore Case option to perform a case insensitive search.

With the Search button will start the search, you can cancel it with the Cancel button.

Searching text in table rows

When selecting one of the tables in the database, the search is performed in all fields containing text:

In the top panel are listed all records with one or more fields matching the string or expression, identified by its primary key, along with the fields in which matches are found. By selecting any one of them you can view the contents of all fields in the bottom panel.

If you want to replace the text with a different one, you have to check the Replace checkbox, at the right of the field, and click the Replace button. You can also delete the record with the Delete button. Obviously, you must use these options very carefully, so they are only available to act on the selected record.

Searching text in the procedures an views code

You can also search within the code of stored procedures and views, when that type of objects is selected.

In the search results panel, the names of objects where matches are found are listed. By selecting one of them, their code will appear in the bottom pane, along with a list of matches found. The start character position within the code and the length of the string found is indicated between parentheses. When selecting one of these matches, the code moves to it and it appears selected.

Here you can also make replacements, marking the matches you want to replace and using the Replace button. You can also drop the view or procedure with the Delete button.