« March 2005 | Main | May 2005 »
April 14, 2005
Where's My Data?
At my current job, we have an Oracle database with several hundred tables. This database holds all the information for a piece of web-based software with many features and hidden corners.
Typically, when a user fills out a form, one or many tables get updated. However, it can get tricky when you are not sure which tables were affected.
Luckily, Oracle has generated tables available called ALL_TAB_COLUMNS and USER_TAB_COLUMNS, which show every column and table in either all schemas or whatever schema you are currently using. With this table, I can loop through each column in each table and scan for a specific value.
For example, let's say that there is a row in some table that has 'Elm St.' somewhere inside it but I am not sure of the table or column name. All I would need to do is select every column and its associated table from ALL_TAB_COLUMNS and then check each column for that value.
Here is a Java program that will do just that. It has come in very handy for me because in a few seconds I can know each table and column in the database that contains a certain value.
Posted by Chuck at 04:00 PM | Comments (0) | TrackBack